This discussion is archived
5 Replies Latest reply: Feb 13, 2013 7:07 AM by HamidHelal RSS

On_Insert goes into loop

925027 Newbie
Currently Being Moderated
Hi,

I have a requirement that 2 records should not contain the same value in one of the fields.

The form actually has an on_insert trigger that calls a procedure that will insert the record into the database table. For each record on_insert fires once and odes the insert.
Now as per the requirement, if a person enters multiple rows in one go without saving and the saves all records together, I am trying to raise error if one of the record has the duplicate value in the field when compared to another line.

Inside the procedure for inserting, I wrote the code like this:

If record1.field1 = record2.field1

then
raise form_trigger_failure;
else

call insert procedure;

end if;

But what is happening is when say 2nd line has duplicate value in field 1, then the error is shown, but on clicking ok button, the on_insert trigger is being called again for line 1 and line 2 causing an infinite loop which causes the same message to keep appearing again and again.

What am I doing wrong?

Regards,
Sarath

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points