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.
What am I doing wrong?
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.
why you want to check when insertion, prevention is better then cure..
stop user inputting duplicate data in the item by When-Validate-Record
follow François Degrelle suggestion. It's very helpful.
Hope this helps
Edited by: HamidHelal on Feb 13, 2013 9:06 PM