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

SQL Server Setting Triggers Firing Order

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 118,620   
Support : SQL Server 2005,2008,2012
 

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.


YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
13 FEB
ASP.NET Core Development (online)

07:00 AM-09:00 AM IST( MON, WED, FRI)

Know More
25 JAN
Angular2 and Angular1 Development (online)

09:00 PM-10:30 PM IST(MON-FRI)

Know More
24 JAN
ASP.NET MVC with Angular2 Development (online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

Know More
20 JAN
NODEJS & MEAN Stack 2 Development (online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

Know More
15 JAN
PPC Marketing (offline)

04:00 PM-05:300 PM

14 JAN
Xamarin Forms : Build Cross-platform Apps (offline)

05:00 PM-06:30 PM IST

10 JAN
ASP.NET MVC with AngularJS Development (online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

3 JAN
ASP.NET MVC with AngularJS Development (online)

07:00 AM-09:00 AM IST

31 DEC
ASP.NET MVC with AngularJS Development (offline)

09:30 AM-11:00 AM

31 DEC
Angular2 and Angular1 Development (offline)

11:00 AM-12:30 AM

26 NOV
ASP.NET MVC with AngularJS Development (offline)

03:00 PM-04:30 PM

5 NOV
ASP.NET MVC with AngularJS Development (offline)

08:00 AM-09:30 AM

4 SEP
MEAN Stack 2 Development (offline)

11:00 AM-12:30 PM.

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+