I have a trigger on a table 'visits'(after insert for each row) that calls a function that inserts a row in the print_queue table. The function should only insert a row if the object_id does not allready exists in the print_queue table. This trigger (may fire multiple times mili seconds apart) as rows are being inserted by an interface file being read. I would think that once the trigger fires the first time that subsequent rows would be excluded when the trigger fires the second time.
The code in the function opens a cursor in a for loop using a master id for the bill records.
for billcnt as bill_listing_cursor dynamic scroll cursor for
Select xyz,objectid from foo f
where not exists (select * from print_queue p where p.objectid = f.objectid)
and f.mast_bill_id = 10112
Do
insert into print_queue objectid ...
end for
This works fine, the problem is when the next record is read and inserts into visits, the trigger is firing and inserts additional print_queue records even though it just loaded the same one. I have verified the sql for the cursor is properly excluding the values once a row has entered. Can this be some kind of timing issue where the inserted row from the first bill, has not had time to be recognized when the next bill comes in ?
The cursor is used because of timing issues there may be more than one record that needs to be printed. If there are 5 records to print, and the interface inserts three visits, I get 15 records in the print_queue instead of five. I would have thought the 'not exists' would prevent the records from being inserted multiple times. What am I missing here ...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.