Shailendra Chauhan
05
DecSometimes 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.
Take our free skill tests to evaluate your skill!
In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.
