Introduction
In this article, we will learn about the Execution order of Triggers In SQL Server.
Triggers in SQL Server
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.
Find more about SQL Queries here: SQL Queries
Triggers are used mainly in the following events
- Insert Data into a table
- Delete data from the table
- Update table record
We can create more than one trigger for the same event (in other words an INSERT, DELETE, or UPDATE transaction). There is one problem, however. Triggers don't have a specified execution order. Execution of triggers is 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 follows
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
![table]()
Now we create two triggers for the insert event.
Create the first trigger
CREATE TRIGGER TRIGGER_SECOND
ON Employee
AFTER INSERT
AS
BEGIN
PRINT ' MY EXECUTE ORDER IS SECOND'
END
Now create 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.
@stmttype- defines the type of trigger, whether insert, delete or update trigger, LOGON, or any Transact-SQL statement event listed in DDL Events.
| Value |
Order |
| First |
Execute order is first |
| Last |
Execution order is last |
| None |
Execution order is #ff0000 |
@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
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
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 defined but the order of TRIGGER_THIRD and TRIGGER_FOURTH is not defined so these both trigger executes in random order b/w TRIGGER_FIRST and TRIGGER_SECOND.
Example
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 the first for the same table, database, or server for the same statement type. This restriction also applies to the last triggers.
Example
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
![Output]()
The preceding query provides all the information about triggers, like the order of execution of triggers, trigger names, last modification date, and type of trigger.
Conclusion
In this article, we learned about the execution order of triggers with code examples in SQL Server.