5 Replies Latest reply: Feb 13, 2013 9:07 AM by HamidHelal RSS

    On_Insert goes into loop

    925027
      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