RSS

Triggers

16 Jun

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.

Advertisements
 
3 Comments

Posted by on June 16, 2011 in Triggers

 

Tags:

3 responses to “Triggers

  1. JamesD

    June 19, 2011 at 2:12 pm

    I believe in Postgres, triggers are fired off in alphabetical prder. Here’s from the documentation:

    “SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient.”

    http://www.postgresql.org/docs/8.4/static/sql-createtrigger.html

     
    • Amit Srivastava

      July 1, 2011 at 9:20 pm

      Hi James,

      “SQL specifies that multiple triggers should be fired in time-of-creation order. PostgreSQL uses name order, which was judged to be more convenient.”

      Above statement is correct if we want to fire Multiple Triggers in the order in which they are created, above scenario came into picture when we want second trigger to be fired first before first. if we do not use concept of sp_settriggerorder, we are not able to fire second trigger first.

      Also above logic is only meant for when maximum number of triggers is 3.

      Hope this will help!!!

      Amit

       
  2. Dhiraj Gupta

    July 13, 2011 at 6:17 pm

    Nice one 🙂

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: