Database : SQL Server Setting Triggers Firing Order

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')