Tuesday, 27 September 2011

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.

1 comment:

SQL Begineer said...

It is very interesting to have all these in one simple post. very impressive work !!!