Sunday 25 March 2012

SQL 2008R2 - Trigger order

I was troubleshooting a piece of code for a client recently and came across an interesting issue.  The client had created 2 triggers on a table that were firing at the same time.  This is pretty much against standard coding practice for several reasons - firstly how can you be sure in which order they will fire, and secondly it is very tricky to debug (due to the first point :)).


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


No comments:

Post a Comment