Specifying First and Last Triggers
You can specify that one of the AFTER triggers associated with a table be either the first AFTER trigger or the last AFTER trigger executed for each of the INSERT, DELETE, and UPDATE triggering actions. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.
To specify the order for an AFTER trigger, use the sp_settriggerorder stored procedure. The options available are:
- First
Specifies that the trigger is the first AFTER trigger fired for a triggering action.
- Last
Specifies that the trigger is the last AFTER trigger fired for a triggering action.
- None
Specifies that there is no specific order in which the trigger should be fired. Used mainly to reset a trigger from being either first or last.
This is an example of using sp_settriggerorder:
sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE'
Important The first and last triggers must be two different triggers.
A table may have INSERT, UPDATE, and DELETE triggers defined on it at the same time. Each statement type can have its own first and last triggers, but they cannot be the same triggers.
If the first or last trigger defined for a table does not cover a triggering action, such as not covering FOR UPDATE, FOR DELETE, or FOR INSERT, there is no first or last trigger for the missing actions.
INSTEAD OF triggers cannot be specified as first or last triggers. INSTEAD OF triggers are fired before updates are made to the underlying tables. However, if updates are made by an INSTEAD OF trigger to underlying tables, the updates occur after triggers defined on the table, including the first trigger. For example, if an INSTEAD OF trigger on a view updates a base table and the base table contains three triggers, the three triggers in the table fire before the data is inserted by the INSTEAD OF trigger. For more information, see Specifying When a Trigger Fires.
If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute is dropped and the order value is set to None; the order must be reset with sp_settriggerorder.
The OBJECTPROPERTY function reports whether a trigger is a first or last trigger using the properties ExecIsFirstTrigger and ExecIsLastTrigger.
Replication generates a first trigger automatically for any table that is an immediate or queued update subscriber. Replication requires that its trigger is the first trigger. Replication raises an error if you try to make a table that has a first trigger an immediate or queued update Subscriber. If you make a user-defined trigger a first trigger after a table has been made an immediate or queued update Subscriber, sp_settriggerorder returns an error. If you use ALTER on the replication trigger, or use sp_settriggerorder to change the replication trigger to a last or none trigger, the subscription does not work correctly.