June 14, 2005 at 5:02 pm
What would be the best method to use update a table from the results of the update of another table?
My Current process uses a Trigger on table1 that inserts records to table2. On insert of table2 another trigger fires to insert records to table3. The problem I'm having is that our process inserts more than 1 record at a time(i.e.300 records) to table1 and fires both triggers for each record sequencially. Is there a method that will allow the first trigger to complete for all the (300 records) before the 2nd trigger fires? Otherwise, the 2nd trigger fires more than necessary and also creates duplicate records.
June 14, 2005 at 8:50 pm
on the first trigger :
CREATE TRIGGER...
FOR Insert
AS
SET NOCOUNT ON
Insert into dbo.Table2 (Col1, Col2, Coln) Select Col1, Col2, Coln from Inserted
GO
Repeat process for the next trigger. This will do the update in only 2 steps.
June 14, 2005 at 9:19 pm
Unfortunatly, it isn't that simple. I need trigger 1 on table1 to complete on (all records) being inserted prior to trigger 2 on table 2 firing. I've tried executing both triggers on table1 and setting the firing order accordingly but when trigger fires it creates a new record for each record inserted by trigger 1. Not good. I need trigger 2 to fire after all records have been inserted by trigger 2.
June 14, 2005 at 9:43 pm
It is that simple, unless I don't understand the question.
Do you have multiple triggers that insert in the same target table?
Can you post the code you're using with its problems?
June 15, 2005 at 9:05 am
Looks like you have multiple triggers in the same table!
If that is the case you should either consolidate the logic into one! That way you are granted synchronous execution or make sure you use only a max of 3 because you can specify only first and last !
cheers
* Noel
June 15, 2005 at 11:15 am
In order to do that, you must ensure that you are using set-based operations in the triggers. If you must use a cursor, use an INSTEAD OF INSERT trigger and transform the rows into a table variable so that you can write the rows to the base table using a single INSERT statement.
FOR INSERT and AFTER INSERT triggers fire after the insert operation completes. INSTEAD OF INSERT triggers fire in place of an INSERT--you have to have an INSERT statement to complete the operation.
INSTEAD OF INSERT triggers fire in place of regular triggers unless the INSERT statement happens within the body of an INSTEAD OF INSERT trigger. In that case, the FOR and AFTER INSERT triggers fire.
A note about the behavior of IDENTITY and rowversion columns within INSTEAD OF, FOR, and AFTER INSERT triggers:
In an INSTEAD OF INSERT trigger, neither the IDENTITY column nor the rowversion column are itialized; in a FOR or AFTER INSERT trigger, the IDENTITY columns contain the new IDENTITY values, and the rowversion column contains the new rowversion values.
You should avoid performing additional updates to the same table within a FOR or AFTER trigger. Use an INSTEAD of trigger to combine the updates into a single set-based statement.
Brian
MCDBA, MCSE+I, Master CNE
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply