I decided to do some testing of this to see if there is a consistent pattern to the trigger firing order. To do this i created a table, trigger_test, and 2 AFTER INSERT triggers, add_two, and times_2. As the names suggest, add_two will take the "end_value" and add 2 to it, updating the end_value field with this new value. Trigger times_two will multiply the end_value and update the field with this new value. Here is code i used for this:
CREATE TABLE trigger_test
(
id INT IDENTITY,
initial_value INT,
end_value INT,
trigger_add BIT,
trigger_times BIT
)
GO
CREATE TRIGGER add_two ON trigger_test AFTER INSERT
AS
BEGIN
UPDATE trigger_test
SET end_value = z.end_value + 2, trigger_add = 1
FROM INSERTED i
INNER JOIN trigger_test z
ON i.id = z.id
END
GO
CREATE TRIGGER times_two ON trigger_test AFTER INSERT
AS
BEGIN
UPDATE trigger_test
SET end_value = z.end_value * 2, trigger_times = 1
FROM INSERTED i
INNER JOIN trigger_test z
ON i.id = z.id
END
GO
Therefore if we insert the value 5 into the table, if trigger add_two occurs first, followed by times_two, we would expect a final value of 14. If it is the other way round the result would be 12. In this way we can easily determine in what order the triggers are executing.
So next we run the insert, and select the results:
INSERT INTO trigger_test (initial_value,end_value) VALUES (5,5)
GO
SELECT * FROM trigger_test
Results:
(1 row(s) affected)
id initial_value end_value trigger_add trigger_times
----------- ------------- ----------- ----------- -------------
1 5 14 1 1
(1 row(s) affected)
Here we clearly see that add_two is executing first, (5+2 = 7, 7*2 = 14).
But what happens if we drop the triggers and create them in the opposite order, times_two before add_two? i'll not repeat the code, as it's the same as above but the result then becomes:
(1 row(s) affected)
id initial_value end_value trigger_add trigger_times
----------- ------------- ----------- ----------- -------------
1 5 12 1 1
(1 row(s) affected)
The result is now 12, indicating that times_two occurred first.
(The test can be repeated multiple times to ensure there is a definite order)
So it would appear that the order the triggers are created in is the order they will be executed in. Or put differently, the lower OBJECT_ID will be executed before the larger one.
This alone is fine and dandy, but may result in developers inadvertently messing things up if they modify and drop/create a procedure, and are unaware that the order is significant. In doing so they could accidentally change the order and therefore the functionality of your code, and as stated earlier, if you ARE going to fire multiple triggers at the same time it will now be very tricky to find the problem!
This can be partially resolved by using a handy proc called sp_settriggerorder. As it's name suggests, this allows you to set the trigger firing order by setting 'FIRST' and 'LAST' values:
EXEC sp_settriggerorder @triggername = 'times_two',
@order = 'first',
@stmttype = 'insert',
@namespace = null
Now we can define the specific order we want the triggers to fire in. Problem solved? Almost....
We can only set 'first' and 'last' values here, so this can only define the order of up to three triggers - set one as 'first', one as 'last' and do nothing for the other and it will have to fire second. Any more triggers and it will not be possible to set the order they will fire.
Also, the same caveat still applies - if a trigger is explicitly set to 'first' and is then dropped and recreated, it will no longer have this setting and sp_settriggerorder will need to be rerun.
For more info on sp_settriggerorder see the msdn reference here: http://msdn.microsoft.com/en-us/library/ms186762.aspx
