Execution Order of Triggers In SQL

This article explains the 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] [intNOT NULL,  
  5.     [Emp_Name] [nvarchar](50) NOT NULL,  
  6.     [Emp_Salary] [intNOT 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 = ONON [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.

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 #ff0000

@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

Output


The preceding query provides all the information about triggers, like order of execution of triggers, trigger names, last modification date and type of trigger.