January 25, 2007 at 10:59 am
Hi
My problem is a simple one I think. We have two tables that are populated by another system that we have no control over. It's a classic master/detail thing related to Orders entered in this other system. Order header is written and then the detail lines go into the second table. I want to be able to take these orders, take some info from the header, concatenate some info from the detail lines and insert a single row in another table.
We are using SQL 2000, and as I understand it an insert trigger will fire before the row is actually inserted. I do not know how many detail lines may be inserted per order (order items) so I don't know when all inserts are complete.
I want to be able to do this when an order is written to the table but obviously the detail lines may well not be there.
We can schedule a process that will check every minute for new orders but need it to be more 'real time'.
Idea's and suggestions welcomed
January 25, 2007 at 12:01 pm
From your experience on the system can you specify if both master and detail tables are getting inserted in the same transaction boundaries? Triggers are realtime solution but you may want to be more specific on what you want to save.
Prasad Bhogadi
www.inforaise.com
January 26, 2007 at 2:44 pm
I would advise not using a trigger for this because if the trigger fails, the transaction that fired it will be rolled back. You don't want to hear that your trigger has been cancelling orders!!!
Triggers are fired after the insert/update/delete or instead of insert/update/delete. They can not be fired before the insert/update/delete. I think what you are thinking of is that the DML action has not yet been committed. It has been inserted/updated/deleted, just not committed. Meaning it will be rolled back if the trigger fails (un-inserted/un-updated/undeleted).
If you want it more real time, then schedule the process to run more frequently, like every 15 seconds or so.
January 26, 2007 at 2:57 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply