SQL Server Setting Triggers Firing Order

SQL Server Setting Triggers Firing Order: An Overview

Sometimes we have many triggers for the same event(s). In that situation, we cannot predict the order in which triggers will fire. The firing sequence of the trigger(s) can be essential when implementing business logic. To address this issue, SQL Server, particularly in scenarios such as a SQL Server Tutorial or SQL Server Certification Course, allows us to specify the firing order of triggers on the same event(s).

Syntax for setting trigger firing order

sp_settriggerorder @triggername='trg_name', @order='FIRST|LAST|NONE', @stmttype='INSERT|UPDATE|DELETE|CREATE_INDEX,ALTER_INDEX', @namespace='DATABASE|SERVER|NULL' 

A simple example for setting trigger firing order

CREATE TABLE dbo.TestTable (
ID int NOT NULL,
Description varchar(100)
)
GO
--Now create triggers on above created table at same event INSERT
CREATE TRIGGER dbo.trg_i_TriggerOrder1
ON dbo.TestTable
AFTER INSERT
As
PRINT 'I will be fired first.'
 GO
CREATE TRIGGER dbo.trg_i_TriggerOrder2
ON dbo.TestTable
AFTER INSERT
AS
PRINT 'I will be fired last.'
GO
CREATE TRIGGER dbo.trg_i_TriggerOrder3
ON dbo.TestTable
AFTER INSERT
AS
PRINT 'I won''t be first or last.'
GO 

This SQL script creates a table called TestTable with the fields ID and Description. Three triggers are then constructed to run following an insert operation on TestTable, each showing a unique message. The first trigger, trg_i_TriggerOrder1, will be fired first, followed by the second trigger, trg_i_TriggerOrder2, and the third trigger, trg_i_TriggerOrder3, whose firing order is not specified.

Set Triggers Firing Order

--Now set triggers firing orders 
EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder1', 'First', 'INSERT'
EXEC sp_settriggerorder 'dbo.trg_i_TriggerOrder2', 'Last', 'INSERT'
--The order of firing the third trigger 'dbo.trg_i_TriggerOrder3' will be between above two 

In this SQL example, sp_settriggerorder specifies the firing order for triggers. The first trigger, trg_i_TriggerOrder1, is configured to fire first during an INSERT operation, whereas trg_i_TriggerOrder2 is configured to fire last. The firing order of trg_i_TriggerOrder3 will be between the first and last trigger.

--Insert data to see trigger firing order
INSERT dbo.TestTable(ID,Description)VALUES (1,'Trigger firing order') 

This SQL code adds data to the TestTable. The triggers that were previously configured to fire on INSERT operations will execute in the desired order, illustrating their firing sequence.

Read More

 Top SQL Server Interview Questions and Answers

Summary

In this article, I try to explain how you can set trigger firing orders with a simple example. I hope after reading this article you will be able to understand the firing order of triggers in the SQL Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE