Triggers Execution Order in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Triggers Execution Order in SQL Server with Examples

In this article, I am going to discuss the Triggers Execution Order in SQL Server with Examples. Please read our previous article where we discussed DDL Triggers in SQL Server with examples. At the end of this article, you will understand how to set the execution order of triggers using the sp_settriggerorder system stored procedure. 

Note: The Server-Scoped Triggers in SQL Server are always fired before any of the databases scoped triggers and we cannot change this execution order.

Example to understand Triggers Execution Order in SQL Server:

Let’s create two DDL Triggers with one database Scoped and one Server-Scoped. We need to create the Database Scoped Trigger in a specific database.

CREATE TRIGGER tr_DatabaseScopeDDLTrigger
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    Print 'Database Scope DDL Trigger'
END
GO

CREATE TRIGGER tr_ServerScopeDDLTrigger
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
    Print 'Server Scope DDL Trigger'
END
GO

Here we have created one database-scoped (tr_DatabaseScopeDDLTrigger) and one server-scoped (tr_ServerScopeDDLTrigger) DDL trigger and both the triggers handling the same DDL event i.e. CREATE_TABLE.

Let’s create a table and see what happens

CREATE TABLE Employee(ID INT, Name VARCHAR(100))

When we execute the above create statement it will give us the below output.

So, from the above output, it proofs that the Server-Scoped DDL Trigger is always fired before the Database-Scoped DDL Trigger is fired in SQL Server. 

We can use the sp_settriggerorder system stored procedure to set the execution order of Server-Scoped or Database-Scoped DDL triggers in SQL Server. 

The sp_settriggerorder system stored procedure has 4 parameters 

1. @triggername: The Name of the Trigger
2. @order: The order value can be First, Last, or None. When we set to None, the trigger is fired in random order
3. @stmttype: The SQL statement that fires the trigger and the value can be INSERT, UPDATE, DELETE, or any DDL event
4. @namespace: The namespace determines the Scope of the trigger and the value can be DATABASE, SERVER, or NULL

To understand the execution order, Let’s create another database scoped DDL Trigger as shown below

CREATE TRIGGER tr_DatabaseScopeDDLTrigger
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    Print 'Database Scope DDL Trigger'
END
GO

Now set the Execution order using the sp_settriggerorder system stored procedure as shown below.

EXEC	sp_settriggerorder
  @triggername = 'tr_DatabaseScopeDDLTrigger1',
  @order = 'FIRST',
  @stmttype = 'CREATE_TABLE',
  @namespace = 'DATABASE'
GO

Now create a table and see the order

CREATE TABLE Employee1(ID INT, Name VARCHAR(100))

When we execute the above create table statement, it will give us the below output.

If we have both a database-scoped and a server-scoped DDL trigger handling the same event and if we have to set the execution order at both levels. Here is the execution order of the triggers.

1. The server-scope trigger set as First
2. Then other server-scope triggers
3. Next, the server-scope trigger set as Last
4. The database-scope trigger set as First
5. Then other database-scope triggers
6. The database-scope trigger set as Last 

In the next article, I am going to discuss Creating and Managing Users in SQL Server with examples. Here, in this article, I try to explain the Triggers Execution Order in SQL Server step by step with an example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.