Execution Order of Triggers In SQL

Triggers are stored programs that are automatically executed or fired when a specified event occurs. It is a database object that is bound to a table and is executed automatically. We cannot call triggers explicitly. Triggers provide data integrity and are used to access and check data before and after modification using DDL or DML queries.

Triggers are used mainly in the following events:

1. Insert Data into table
2. Delete data from table
3. Update table record

We can create more than one trigger for the same event (in other words an INSERT, DELETE, UPDATE transaction). These is one problem, however. Triggers don't have a specified execution order. Execution of triggers are performed randomly. Sometimes the business logic dictates that we need to define two triggers on a table that must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one for our logic to be implemented correctly.

Today we learn how to define the execution order of triggers.

First we create a table as in the following:

1. GO  
2.   
3. CREATE TABLE [dbo].[Employee](  
4.     [Emp_ID] [int] NOT NULL,  
5.     [Emp_Name] [nvarchar](50) NOT NULL,  
6.     [Emp_Salary] [int] NOT NULL,  
7.     [Emp_City] [nvarchar](50) NOT NULL,  
8.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
9. (  
10.     [Emp_ID] ASC  
11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
12. ) ON [PRIMARY]  
13.   
14. GO  

Now insert some values into the table.

1. Insert into Employee  
2. Select 1,'Pankaj',25000,'Alwar' Union All  
3. Select 2,'Rahul',26000,'Alwar' Union All  
4. Select 3,'Sandeep',25000,'Alwar' Union All  
5. Select 4,'Sanjeev',24000,'Alwar' Union All  
6. Select 5,'Neeraj',28000,'Alwar' Union All  
7. Select 6,'Naru',20000,'Alwar' Union All  
8. Select 7,'Omi',23000,'Alwar'   

Select all the values from the table.



Now we create two triggers for the insert event.

Example 1

1. CREATE TRIGGER TRIGGER_SECOND  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS SECOND'  
9. END  

Now create a another trigger.

1. CREATE  TRIGGER TRIGGER_FIRST  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS FIRST'  
9. END  

Now we insert data into the employee table.

1. INSERT INTO Employee VALUES(11,'DIV',24000,'JAIPUR')  

Output 

MY EXECUTE ORDER IS SECOND
MY EXECUTE ORDER IS FIRST

(1 row(s) affected)

We can see that the order of execution of the triggers may depend upon the order of their creation. By default, multiple triggers on a SQL Server table for the same action are not fired in a guaranteed order.

Now we learn how to define the execution order of triggers.

SQL Server contains a sp_settriggerorder Stored Procedure for defining the execution orders of triggers.

Syntax of sp_settriggerorder

1. sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘  
2. , [ @order = ] ‘value‘  
3. , [ @stmttype = ] ‘statement_type‘  
4. [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]   

A brief explanation of the arguments follows.

[ @triggername= ] '[ triggerschema.] triggername' : It defines the trigger name and schema name to which it belongs.

@order: defines the execution order of a trigger. The value is a varchar(10) and it can be any one of the following values.
 

Value	Order
First	Execute order is first
Last	Execution order is last
None	Execution order is undefined

@stmttype: defines the type of trigger, whether insert, delete or update trigger, LOGON, or any Transact-SQL statement event listed in DDL Events. 

@namespace: SQL Server 2005 specific and indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger.

Now to see some examples.

Example 2
In the preceding example we create two triggers, TRIGGER_FIRST and TRIGGER_SECOND. Now we define the order of both triggers.

First we set the order of TRIGGER_FIRST.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FIRST',  
2.    @order = 'FIRST',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  

Now we set the order of TRIGGER_SECOND.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_SECOND',  
2.    @order = 'LAST',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  

After defining the order of execution now we insert some data into the table and examine the result.

1. INSERT INTO Employee  
2.     VALUES (10, 'DEV', 25000, 'JAIPUR')  

Output

MY EXECUTE ORDER IS FIRST
MY EXECUTE ORDER IS SECOND

(1 row(s) affected)

As we expect, trigger TRIGGER_FIRST executes first then trigger TRIGGER_SECOND executes.

Example 3

Now we create 2 more triggers and define their order.

1. CREATE  TRIGGER TRIGGER_FOURTH  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS FOURTH'  
9. END  

And

1. CREATE  TRIGGER TRIGGER_THIRD  
2. ON Employee  
3. AFTER INSERT  
4.   
5. AS  
6. BEGIN  
7.   
8.     PRINT ' MY EXECUTE ORDER IS THIRD'  
9. END  

Now we define the orders of these two triggers.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FOURTH',  
2.    @order = 'NONE',  
3.    @stmttype = 'INSERT',  
4.    @namespace = NULL  
5.   
6. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',  
7.    @order = 'NONE',  
8.    @stmttype = 'INSERT',  
9.    @namespace = NULL  

Now insert some data into the table and examine the result.

1. INSERT INTO Employee  
2. VALUES (10, 'DEV', 25000, 'JAIPUR')  

Output

MY EXECUTE ORDER IS FIRST
MY EXECUTE ORDER IS FOURTH
MY EXECUTE ORDER IS THIRD
MY EXECUTE ORDER IS SECOND

We can see that order of TRIGGER_FIRST and TRIGGER_LAST is define but order of TRIGGER_THIRD and TRIGGER_FOURTH is not define so these both trigger execute in random order b/w TRIGGER_FIRST and TRIGGER_SECOND.

Example 4

Let us see another example.

1. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FIRST',  
2.      @order = 'FIRST',  
3.      @stmttype = 'INSERT',  
4.      @namespace = NULL  
5.   
6. EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',  
7.      @order = 'FIRST',  
8.      @stmttype = 'INSERT',  
9.      @namespace = NULL  

Output

 

Msg 15130, Level 16, State 1, Procedure sp_settriggerorder, Line 163
There already exists a 'FIRST' trigger for 'INSERT'.
 

When we run the preceding query SQL Server throws an error because we can't provide FIRST and LAST order to more than one trigger. If a first trigger is already defined on the table, database, or server, we cannot designate a new trigger as first for the same table, database, or server for the same statement type. This restriction also applies to last triggers.

Example 5

1. SELECT  
2.     sys.TABLES.name,  
3.     sys.TRIGGERS.name,  
4.     sys.TRIGGER_EVENTS.type,  
5.     sys.TRIGGER_EVENTS.TYPE_DESC,  
6.     IS_FIRST,  
7.     IS_LAST,  
8.     sys.TRIGGERS.CREATE_DATE,  
9.     sys.TRIGGERS.MODIFY_DATE  
10. FROM sys.TRIGGERS  
11. INNER JOIN sys.TRIGGER_EVENTS  
12.     ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id  
13. INNER JOIN sys.TABLES  
14.     ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID  
15. ORDER BY MODIFY_DATE  

Output