27 Sept 2011

SQL : Triggers

Hello Folks!! 

This time, I come up with one of the very basic but important concepts of the SQL Database in the name of TRIGGERS.

As a matter of fact, Trigger is nothing but a SQL statement which supposed to get invoke after certain events such as deletion, insertion and updation of the records happened in the base table.

Below is the example of trigger.

For e.g., let say requirement is to insert data into the Table TABLE_B as soon as any record is inserted into the TABLE_A, ideal and best solution by far is to create INSERT TRIGGER on TABLE_A that will insert the data into TABLE_B as soon as record is inserted into TABLE_A.

Easily, INSERT trigger can be created with below definition.

            CREATE TRIGGER DBO.TRIG
                ON TABLE_A 
                    FOR [INSERT]
                        AS INSERT INTO TABLE_B VALUES(VALUE)
With the help of above code snippet INSERT TRIGGER will be created, now what will happen is when ever there is any event of insertion of records in TABLE_A, trigger name DBO.TRIG will also get executed and a record will get inserted into TABLE_B based on definition of the trigger DBO.TRIG.

Till here, things look quite obvious, pretty simple and straight forward and follows what SQL suggests.

Now, let's move to the more complicated requirement which has been detailed below.

Requirement: 

Let's say, on one very good morning, you have login into your machine, and you have got requirement from the client which says, to create multiple triggers on the table in such a way that sequence of the trigger's execution must be in a particular order, i.e. Trigger2 supposed to execute after execution of Trigger1.

If we break down the requirement, it will be detailed as below 
  1. We have table TABLE_A where we need to INSERT data and manage TRIGGERS
  2. Need to implement two triggers say TRIGGER1 and TRIGGER2.
  3. To configure order of execution of the Triggers on the table, TRIGGER2 to be followed by TRIGGER1
Resolution

In order to cater above requirement, we need following objects to be in place.
  1. Base table i.e. TABLE_A
  2. Target Table  i.e TABLE_B
  3. More than one trigger i.e. TRIGGER1 & TRIGGER2
  4. SQL command i.e. to execute the query.
In general, we never bother about sequence of the execution of the triggers, this seems to be a very rare requirement, i.e. to execute multiple triggers in a very specific order, as in default, we have no specific control over the order in which triggers executes, i.e. we don't have any control which stats which triggers will run first, at least by default, 

After, doing so much research and development, I have come across one of the in-built system stored found that there is one System in built stored procedure which can be helpful on achieving this feet, and stored procedure named as SP_SETTRIGGERORDER.

SP_SETTRIGGERORDER :
  •    This is In-built system stored procedure.
  •    This will govern the execution order of multiple triggers based on same table.
  •    Can be called using below syntax (required parameter to pass).
EXEC SP_SETTRIGGERORDER triggername = trigger_name @order = [FIRST|LAST|NONE]
	,@stmttype = [INSERT|UPDATE|DELETE|]
	,@namespace = [DATABASE|SERVER|NULL]
Let's explore more on how to control the sequence order of the multiple triggers on the same or Base table.

Let's start with two triggers on same table and say we have created two triggers such as TRIGGER1 and TRIGGER2.

By Default, sequence order of the triggers on any table is on accord with when they are created, i.e. which ever trigger created first will execute first followed by others those are created just after the previous one

As in this case, TRIGGER1 created first followed by TRIGGER2, hence by default, TRIGGER1 will execute first and then followed by TRIGGER2 as shown as below.

Sequence of execution of the triggers on the table.

1. TRIGGER1 
2. TRIGGER2

Things are pretty much good and obvious but real problems comes up when we want particular trigger to execute first irrespective of when it was created OR any how, very first created trigger got deleted and have re-created the same, though we have triiger back but lost the sequence of the order of the execution.

In case, TRIIGGER1 was deleted and re-created, sequence order of the execution will be as follows

1. TRIGGER2 
2. TRIGGER1

Herein, nothing wrong in the sequence order of the execution of the triggers as it follows guidelines of the SQL of FIRST CREATED FIRST EXECUTED.

Perhaps, above is fine but requirement is to control the sequence of the execution of the triggers, i.e. in this case, need to execute triggers in below sequence 

1. TRIGGER1 
2. TRIGGER2

So, the ask is how to manage this sequence order, and as detailed earlier SP_SETTRIGGERORDER will do job for us, below is the code which can do the needful.

Here comes the role of sp_settriggerorder, we need to some tweaking while providing parameters 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 load records in TABLE_A, TRIGGER1 will always execute first and it will be followed by TRIGGER2

Below is the sequence of the execution that will be performed

1. INSERT COMMAND in TABLE_A
2. TRIGGER1
3. TRIGGER2

Note: TRIGGER1 will always be triggered first, it doesn't matter which trigger is created first.

How to execute in SSMS or at any platform.

In order to execute triggers in managed order, we need to call sp_settriggerorder first with provided paramater and then Insert records in the base table, the complete query for the SQL command is as below : 

EXEC sp_settriggerorder @triggername = Trigger1 @order = FIRST
	,@stmttype = INSERT
	,@namespace = NULL
EXEC sp_settriggerorder @triggername = Trigger2 @order = LAST
	,@stmttype = INSERT
	,@namespace = NULL

INSERT INTO TABLE_A (COL_A) VALUES (value1)
Result 
Message 

          Records Inserted successfully.

            Firing modified Trigger1 

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