Re: Insert Trigger problem

Oracle triggers are fired in the context of your transaction. So, as soon as you commit, your record and any rows added by a trigger are all in the database. There is no dependency between transactions within Oracle. You could explicitly lock the table which would cause other transactions to fail or wait until you commit.

Marc Perkowitz
MTP Systems Consulting

In a message dated 12/15/2000 1:30:44 PM Central Standard Time, hdaiminger_at_vivonet.com writes:

<< Hi!  

 I am having an interesting problem here:  

 A user is inserting a record into table A. This table has an after insert  trigger associated with it that inserts three rows in table RESULT.  Shortly after that, the user inserts a record into table B. This table has  an after insert trigger associated with it that inserts 500 rows in table  RESULT.    Now the user goes back to table A and inserts another record into table A,  which causes the trigger to insert another three recors in the table  RESULTS. Then the user inserts into table B again... And so on and so forth.  

 The question is: does the trigger on table A (when it is called the second  time) wait for the 500 records from the table B trigger to be inserted or  does it fire right away when the user inserts another record?  

 The deal is that we need the records in the RESULT table to be in a specific  order. In other words: the trigger on table A would have to 'wait' for the  trigger on table B (that is inserting far more records) to finish before it  is executing again.  

 Is there any way to do that?  

 This is 8.1.6 on Win2k.  

 Thanks,
 Helmut    

 PS: Does anybody have an idea how SQL Server 7 handles that problem? Because  we would have to do the same thing on SQL Server 7 as well... Received on Fri Dec 15 2000 - 14:11:31 CST