Tuesday, April 28, 2009

Setting Execution Order of Triggers in Sql Server

I had multiple triggers defined for update on a table. I got a requirement to force the execution for the triggers to be done in a defined order. I had searched some websites and found that it is possible using the built in sproc sp_settriggerorder

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


[@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.

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

[@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.