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 ...
For the record trexco, we make extensive use of the "When" clause in our triggers. Query for the condition your looking for. If it doesn't match, then no trigger fires. We've gotten VERY detailed in some of our "When" statements. Keeps you from falling into the trigger logic before you make that decision.
Just wanted to drop that out there.
Do you use source control on your procedures? May want to look to see who modified the procedure. :)
answered 24 Aug '11, 18:43