Triggers Execution Order in SQL Server

Triggers Execution Order in SQL Server

In this article, I will discuss the Triggers Execution Order in SQL Server with an example. Please read the following articles before proceeding to this article.

Database Scoped DDL Trigger in SQL Server

Server-Scoped DDL Trigger in SQL Server

In this article, we will discuss how to set the execution order of triggers using sp_settriggerorder system stored procedure. 

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

Let’s understand Triggers Execution Order in SQL Server with an example.

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.

Triggers Execution Order in SQL Server

So, from the above output, it proofs that the Server-Scoped DDL Trigger is always fired before the Database-Scoped DDL Trigger 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.

Triggers Execution Order in SQL Server

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 the 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 will discuss one real-time example of SQL Server DDL Trigger.

SUMMARY

In this article, I try to explain the Triggers Execution Order in SQL Server step by step with a real-time 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.

Leave a Reply

Your email address will not be published. Required fields are marked *