sp_settriggerorder

Specifies which AFTER triggers associated with a table will be fired first or last. The AFTER triggers that will be fired between the first and last triggers will be executed in undefined order.

Syntax

sp_settriggerorder[@triggername = ] 'triggername'
    , [@order = ] 'value'
    , [@stmttype = ] 'statement_type'

Argument

[@triggername = ] 'triggername'

Is the name of the trigger whose order will be set or changed. triggername is sysname. If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure will return an error.

[@order = ] 'value'

Is the setting for the new trigger order. value is varchar(10) and it can be any of the following values.

Important  The First and Last triggers must be two different triggers.

[@stmttype = ] 'statement_type'

Specifies which SQL statement fires the trigger. statement_type is varchar(10) and can be INSERT, UPDATE, or DELETE. A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. SQL Server will return an error if TR1, which has been defined only as an INSERT trigger, is set as a First (or Last) trigger for an UPDATE statement. For more information, see the Remarks section.

Return Code Values

0 (success) and 1 (failure)

Remarks

There can be only one First and one Last trigger for each of INSERT, UPDATE, or DELETE statement on a single table.

If a First trigger is already defined on the table, you cannot designate a new trigger as First for the same table for the same operation (INSERT, UPDATE, or DELETE). This restriction also applies Last triggers.

As part of SQL Server replication, a First trigger can be designated on a published table; however, if a conflict with the user-defined trigger exists, the designation of the user-defined trigger must be changed to None before the table can be published.

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset with sp_settriggerorder.

If the same trigger has to be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for the statement type.

Permissions

The owner of the trigger and the table on which the trigger is defined has execute permissions for sp_settriggerorder. Members of db_owner and db_ddladmin roles in the current database, as well as the sysadmin server role can execute this stored procedure.

Examples

sp_settriggerorder @triggername= 'MyTrigger', @order='first', @stmttype = 'UPDATE'