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:

GO

CREATE TABLE [dbo].[Employee](
    [Emp_ID] [int] NOT NULL,
    [Emp_Name] [nvarchar](50) NOT NULL,
    [Emp_Salary] [int] NOT NULL,
    [Emp_City] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now insert some values into the table.

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

Select all the values from the table.



Now we create two triggers for the insert event.

Example 1

CREATE TRIGGER TRIGGER_SECOND
ON Employee
AFTER INSERT

AS
BEGIN

    PRINT ' MY EXECUTE ORDER IS SECOND'
END

Now create a another trigger.

CREATE  TRIGGER TRIGGER_FIRST
ON Employee
AFTER INSERT

AS
BEGIN

    PRINT ' MY EXECUTE ORDER IS FIRST'
END

Now we insert data into the employee table.

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

sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘
, [ @order = ] ‘value‘
, [ @stmttype = ] ‘statement_type‘
[ , [ @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.

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

Now we set the order of TRIGGER_SECOND.

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

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

INSERT INTO Employee
    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.

CREATE  TRIGGER TRIGGER_FOURTH
ON Employee
AFTER INSERT

AS
BEGIN

    PRINT ' MY EXECUTE ORDER IS FOURTH'
END

And

CREATE  TRIGGER TRIGGER_THIRD
ON Employee
AFTER INSERT

AS
BEGIN

    PRINT ' MY EXECUTE ORDER IS THIRD'
END

Now we define the orders of these two triggers.

EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_FOURTH',
   @order = 'NONE',
   @stmttype = 'INSERT',
   @namespace = NULL

EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',
   @order = 'NONE',
   @stmttype = 'INSERT',
   @namespace = NULL

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

INSERT INTO Employee
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.

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

EXEC sys.sp_settriggerorder @triggername = 'TRIGGER_THIRD',
     @order = 'FIRST',
     @stmttype = 'INSERT',
     @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

SELECT
    sys.TABLES.name,
    sys.TRIGGERS.name,
    sys.TRIGGER_EVENTS.type,
    sys.TRIGGER_EVENTS.TYPE_DESC,
    IS_FIRST,
    IS_LAST,
    sys.TRIGGERS.CREATE_DATE,
    sys.TRIGGERS.MODIFY_DATE
FROM sys.TRIGGERS
INNER JOIN sys.TRIGGER_EVENTS
    ON sys.TRIGGER_EVENTS.object_id = sys.TRIGGERS.object_id
INNER JOIN sys.TABLES
    ON sys.TABLES.object_id = sys.TRIGGERS.PARENT_ID
ORDER BY MODIFY_DATE

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.