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.
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.
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
- @triggername: The Name of the Trigger
- @order: The order value can be First, Last or None. When we set to None, the trigger is fired in random order
- @stmttype: The SQL statement that fires the trigger and the value can be INSERT, UPDATE, DELETE or any DDL event
- @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 the levels. Here is the execution order of the triggers.
- The server-scope trigger set as First
- Then other server-scope triggers
- Next, the server-scope trigger set as Last
- The database-scope trigger set as First
- Then other database-scope triggers
- The database-scope trigger set as Last
In the next article, I will discuss one real-time example of SQL Server DDL Trigger.
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.