I have a couple of triggers on a table that I want to keep separate and would like to priortize them.
I could have just one trigger and do the logic there, but I was wondering if there was an easier/logical way of accomplishing this of having it in a pre-defined order ?
You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created.
SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers. We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.
What is trigger order? Trigger order is a pre-set order, that users place ahead with an order price and contracts amount (like a limit order), which will only be triggered under specific conditions (a trigger price/trigger). Once the latest traded price has reached the "trigger", the pre-set order will be executed.
Constraint are always checked first in AFTER trigger, whereas BEFORE trigger fires first.
Use sp_settriggerorder. You can specify the first and last trigger to fire depending on the operation.
sp_settriggerorder on MSDN
From the above link:
A. Setting the firing order for a DML trigger
The following example specifies that trigger uSalesOrderHeader be the first trigger to fire after an UPDATE operation occurs on the Sales.SalesOrderHeader table.
USE AdventureWorks; GO sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order='First', @stmttype = 'UPDATE';
B. Setting the firing order for a DDL trigger
The following example specifies that trigger ddlDatabaseTriggerLog be the first trigger to fire after an ALTER_TABLE event occurs in the AdventureWorks database.
USE AdventureWorks; GO sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With