Which Trigger Run First

When multiple trigger is set in a table object, definitely we have a question on mind that which trigger is going to first … which one is going to second and which one is going to last.

Definitely we can set the order of trigger execution, but with some limitations with it. We can set the first preference and last preference of trigger executions of MS SQL, but cannot set the preferences between them.

For an example Table-A Contains 4 triggers, Trigger-1 to Trigger-4. We can set that Trigger-2 is running first and Trigger-1 is running last. But we cannot set the preference of Trigger-3, Trigger-4.  They fire by undefined order.

MS SQL provide us  a system stored procedure to set the executions preferences of trigger.

sp_settriggerorder

       [ @triggername = ] '[ triggerschema. ] triggername'

     , [ @order = ] 'value'

     , [ @stmttype = ] 'statement_type'

       [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

 

[ @triggername= ] '[ triggerschema.] triggername'

It contains the name of triggers. If the Instead of trigger name specified an error.  triggerschema cannot be specified for DDL or logon triggers.

[ @order= ] 'value'

Setting the Order of Executions

Value	Description
First	Trigger is fired first.
Last	Trigger is fired last.
None	Trigger is fired in undefined order.

 

[ @stmttype= ] 'statement_type'

 

It can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement event listed in  DDL events.

 

 

 

@namespace = { 'DATABASE' | 'SERVER' | NULL }

 

When triggername is a DDL trigger, specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified.

 

Posted by: MR. JOYDEEP DAS