Browse Tutorials
SQL Server Setting Triggers Firing Order

SQL Server Setting Triggers Firing Order

24 Mar 2024
Advanced
7.95K Views
4 min read

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.

FAQs

Q1. Why is it essential to specify the firing sequence for triggers in SQL Server?

Setting the firing order guarantees that triggers are executed properly, which is critical for ensuring business logic integrity in complicated database processes.

Q2. In SQL Server, how can I specify the firing order for triggers?

Use the sp_settriggerorder system stored procedure to specify the firing order of triggers depending on their names and the event to which they respond.

Q3. Can I change the firing order for multiple triggers on the same event in SQL Server?

Yes, you can use sp_settriggerorder to define whether a trigger should run first, last, or somewhere in between other triggers on the same event.

Q4. What happens if I do not provide firing orders for triggers in SQL Server?

Without specified firing orders, trigger execution occurs in an arbitrary order, which can lead to unpredictable behavior and compromise database consistency.

Q5. Is it possible to change firing orders dynamically in SQL Server?

Yes, you can change the firing order of triggers at any moment with sp_settriggerorder, providing flexible modifications to meet changing business needs.

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

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this