The Database Engine allows multiple triggers to be created for each table or view and for each modification action (INSERT, UPDATE, and DELETE) on them. Additionally, you can specify the order of multiple triggers defined for a given action. Using the system stored procedure sp_settriggerorder, 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 triggering action. This system procedure has a parameter called @order that can contain three values:
- first – Specifies that the trigger is the first AFTER trigger fired for a modification 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. (This value is generally used to reset a trigger from being either first or last.)
Note – If you use the ALTER TRIGGER statement to modify the structure of a trigger, the order of that trigger (first or last) will be dropped.
Example 14.6 shows the use of the system stored procedure sp_settriggerorder.
Example 14.6
EXEC sp_settriggerorder @triggername = 'modify_budget',
@order = 'first', @stmttype='update'Code language: CSS (css)Note – There can be only one first and one last AFTER trigger on a table. The sequence in which all other AFTER triggers fire is undefined.
To display the order of a trigger, you can use the following:
- sp_helptrigger
- OBJECTPROPERTY function
The system procedure sp_helptrigger contains the order column, which displays the order of the specified trigger. Using the OBJECTPROPERTY function, you can specify either ExecIsFirstTrigger or ExecIsLastTrigger as the value of the second parameter of this function. The first parameter is always the identification number of the database object. The OBJECTPROPERTY function displays 1 if the particular property is TRUE.
Note – Because an INSTEAD OF trigger is fired before data modifications are made to the underlying table, INSTEAD OF triggers cannot be specified as first or last triggers.
