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
call insert procedure;
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.
Please note that if a record is already saved and I am entering a new record with duplicate value from record 1, I have already handled the scenario with WHEN_VALIDATE_RECORD stopping from entering duplicate value. But it is when multiple records are saved in one go and 2 of those records have duplicate value, that I am facing this issue.