Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Setting Triggers Firing Order

 Print 
  Author : Shailendra Chauhan
Posted On : 08 May 2012
Total Views : 122,895   
Updated On : 24 Sep 2016
 

Sometimes we have multiple triggers on the same event(s). In that case we can't predict the firing order of triggers. Sometimes the firing order of trigger(s) is important for us to implement business logic. To solve this issue, In SQL Server we have option to set the firing order of triggers on 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' 

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 

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 
 --Insert data to see trigger firing order
INSERT dbo.TestTable(ID,Description)VALUES (1,'Trigger firing order') 
Summary

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



Free Interview Books
 
COMMENTS
18 NOV
Angular with Firebase (Classroom)
04:00 PM-07:00 PM IST(+5:30)
18 NOV
Angular2+4 with Firebase (Online)
10:30 AM-12:30 PM IST(+5.30 GMT)
11 NOV
ASP.NET MVC (Online)
08:00 AM-10:00 AM IST / 09:30 PM To 11:30 PM(CST)
11 NOV
ASP.NET MVC (Classroom)
09:00 AM to 12:00 AM
7 NOV
Developing Microsoft Azure Solutions (Online)
07:00 AM-08:30 AM IST/ 8:30 Pm -10:00 PM CST
14 OCT
Angular2 and Angular4 (Online)
03:00 PM-05:00 PM IST (+5.30GMT)
12 OCT
ASP.NET Core (Online)
09:00 PM - 11:00 PM IST(+5:30 GMT)
10 OCT
Microsoft Azure Infrastructure Solutions (Online)
08:00 AM-09:30 AM IST / 09:30 PM -11:00 PM CST
LIKE US ON FACEBOOK
 
+