|
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. :) Jeff Gibson Hi Jeff, No as yet we have not implemented scource control in the database. We have it in our applications, but not for the triggers and sp. In fact I have been trying to figure out the best way to implement it. We have MS Source Safe as our in house source control tool. I see how you can export the procedure/trigger and put it in SCC. However I am unsure about how to best implement the editing and changing of these. The interface in ISQL seems a little difficult to use, could just be my limited understanding of the two products. Is there no way to check something in and out from sybase central like when in Powerbuilder or VB.net ? The best way that I have found so far is to operate from source, have it open isql, then save and make the change.
(25 Aug '11, 09:19)
trexco
|
I think we may need some more detail on this trexco.
First. What version of SQL Anywhere are you using??
Second. What is the syntax of your trigger?? (Watcom or Transact)
Just starting with this. I use triggers to generate entries in other tables all over the place. I have never had problems with it firing the insert more than once. Even when you're talking about milliseconds, that's nothing for a database to interpret that.
Let me know on the first two and I'll see if I might be able to point you in the right direction.
Jeff
Can you give some more details on the timing details that require a cursor? At first glance I think I would try not using a cursor at all and having the trigger call the procedure used a plain insert ... on existing skip ...
You could also add some message statements or unload texts to log which item is being used too many times - the trigger, the procedure, or the cursor/insert inside the procedure.
Thanks Jeff and Siger, The problem does not seem to be repeating today. Everyone tells me that nothing was changed in the trigger or sp. I was also of the opinion that it could not happen quick enough to escape detection. Looking into the code it seems that an extra 'If' statement has materialized in the trigger that is preventing the issue from recurring. Thanks for confirming my thoughts on the timing. As a side note I will examine the on existing skip option on the insert also. Thanks again