How to specify the Trigger orders in SQL Server 2012?

If you are working with multiple triggers then you want to specify the order in which the trigger will fire. But in SQL server you have limitation for that, you can only specify the first and last trigger to execute.

The first and last trigger to execute can be specified using the sp_settriggerorder stored procedure. This provides some degree of control over the order in which multiple triggers execute, but it is important to understand that triggers other than the first and last are executed in an undefined order.

DML Triggers:

There can be only one First and one Last trigger for each statement on a single table.

If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. This restriction also applies Last triggers.

DDL Triggers:

If a DDL trigger with database scope and a DDL trigger with server scope exist on the same event, you can specify that both triggers be a First trigger or a Last trigger. However, server-scoped triggers always fire first. In general, the order of execution of DDL triggers that exist on the same event is as follows:

1.       The server-level trigger marked First.

2.       Other server-level triggers.

3.       The server-level trigger marked Last.

4.       The database-level trigger marked First.

5.       Other database-level triggers.

6.       The database-level trigger marked Last.

LOGON Triggers:

Multiple triggers can be defined on the LOGON event. Any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure. SQL Server does not guarantee the execution order of the remaining triggers.

Important:

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 by using sp_settriggerorder.

Learn more on Trigger order with Replication.

Example:

--The following example specifies that trigger trgInsertMessage be the first trigger to fire after an INSERT operation occurs on the Employee table.

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='First', @stmttype = 'INSERT';

--if you want to make it last you can do it as follows:

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='Last', @stmttype = 'INSERT';

--For DDL Trigger

sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';

@namespace: When triggername is a DDL trigger, specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified. If not specified, or if NULL is specified, triggername is a DML trigger.