Tag: sp_settriggerorder in SQL Server

SQL

sp_settriggerorder in SQL Server
Today, we are going to see a very interesting system procedure in SQL Server – sp_settriggerorder. At times, we will have multiple triggers on the table for same operations. If there is a significance in the order of execution, how do we ensure the ordering? The answer is sp_settriggerorder.

Let us look at the syntax quickly:
sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername’ , [ @order = ] ‘value’ , [ @stmttype = ] ‘statement_type’ [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]
triggername -> Name of the trigger to be ordered.
order -> Order of the trigger. It can be (First/Last/None)
stmttype -> INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement DDL event
namespace -> ‘DATABASE’ | ‘SERVER’ | NULL

Sample Execution 
DROP  Table  if exists TestTriggerOrder, TestTriggerResult
GO

Create Table TestTriggerOrder (Id bigint, Value varchar(100))
Create Table TestTriggerResult ( OrderValue varchar(100), EventDateTime Datetime default(Getdate()) )
Insert into TestTriggerOrder Values(1,'SQL')

GO
create trigger tr_upd_TesttriggerOrder On TestTriggerOrder
FOR UPDATE
AS
	Insert into TestTriggerResult(OrderValue) Values ('FirstTrigger')

GO
create trigger tr_upd_TesttriggerOrder0 On TestTriggerOrder
FOR UPDATE
AS

	Insert into TestTriggerResult(OrderValue) Values ('SecondTrigger')

GO
Select * From sys.trigger_events


While executing the above code, we can see the objects are created for our testing and the type of order is not specified in the trigger_events (is_first/is_last values). This is the normal scenario for any triggers, by default, there would not be any specific order defined for the execution.

Now, let us quickly try to set an order and see how the results are changing. 
--First step -> We are trying to update a value in the table
update TestTriggerOrder set Value=value+' Server'

Select * from TestTriggerResult

GO
sp_settriggerorder @triggername= 'tr_upd_TesttriggerOrder0', @order='First', @stmttype = 'UPDATE';

Select * From sys.trigger_events

--Second step -> Again, We are trying to update a value in the table post setting the order
update TestTriggerOrder set Value=value+' Server'

Select * from TestTriggerResult


We can clearly see that the order of values inserted in the TestTriggerResult table has changed as per the order given in the above code.Also, this can be confirmed in the sys.trigger_events, the value of is_first has been changed to 1.

Caveats:

    1. If there is a change in definition of the trigger, then the order has to set again.
    2. Replication always needs its triggers to be the first in order.


I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!