Triggers

Firing of Triggers in Order

• Some time we need triggers to fire in order, in general we have no specific control over the order in which triggers will fire, in order to control the same, i eventually come across to the system stored procedure named sp_settriggerorder.
• sp_settriggerorder.is meant for to provide ordering to the triggers to fire, parameters are as below

sp_settriggerorder_parameters

Exec sp_settriggerorder @triggername = trigger_name
@order =[FIRST|LAST|NONE],
@stmttype =[INSERT|UPDATE|DELETE|],
@namespace = [DATABASE|SERVER|NULL]

Example

• Scenario : We have table say Table1 where we want to insert data, we need to fire two insert triggers on Table1, also we need to fix the order in which these triggers will fire.

• Workaround : First of all, we have created Trigger1 and then Trigger2, we have inserted the record into the table

Sequence of execution are as follow :

1. INSERT command
2. Trigger1
3. Trigger2

Note : if we drop the trigger1 and recreated the same, order of the execution of triggers has been change as below, as system prioritize the triggers as First come first server basis.

Sequence of execution will be as follow :

1. INSERT command
2. Trigger2
3. Trigger1

Here comes the role of sp_settriggerorder, we need to do some tweaking while providing parameters in sp_settriggerorder as below

exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL

exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL

Now if we executed the insert command it will always executed in below mention order.:

Sequence of execution are as follow :

1. insert command
2. Trigger1
3. Trigger2

Note : it doesn’t matter whether trigger1 is created first or last.

Over All order of execution will be as follow

exec sp_settriggerorder @triggername = Trigger1
@order = FIRST,
@stmttype =INSERT,
@namespace = NULL

exec sp_settriggerorder @triggername = Trigger2
@order = LAST,
@stmttype =INSERT,
@namespace = NULL

Insert into table1 (col) values(value1)

Result : 

Message

Firing modified Trigger1

Firing Modified Trigger2

Author : We will discuss on some other topic in next post.