Trigger Ordering in Sql Server

Many of times we face such issue that we have two triggers defined on same table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. So how can we make sure that they fire in the correct order to enforce my business logic.

By default, multiple triggers on a SQL Server table for the same action are not fired in a guaranteed order. However, it's possible to declare the firing order for 2 AFTER triggers (i.e. triggers that fire after the database action has been completed) using system stored procedure sp_settriggerorder. This feature cannot be used with INSTEAD OF triggers and you will receive a database error if you attempt to define an order on these types of triggers.

The system stored procedure sp_settriggerorder was introduced in SQL Server 2000 and has been modified to accept a new parameter in SQL Server 2005 to support the new DDL trigger feature. It is defined as follows:

exec sp_settriggerorder @triggername = , /* SQL Server 2000 and 2005 */
@order = [FIRST|LAST|NONE], /* SQL Server 2000 and 2005 */
@stmttype = [INSERT|UPDATE|DELETE|], /* SQL Server 2000 and 2005 */
@namespace = [DATABASE|SERVER|NULL] /* SQL Server 2005 only */

- Parameter @triggername is self explanatory; it's the trigger being ordered.

- Parameter @order indicates whether the trigger should fire FIRST or LAST. If NONE is specified, then no order is enforced.

- Parameter @stmttype indicates the trigger type i.e. whether it's an INSERT trigger, for instance.

- Parameter @namespace is SQL Server 2005 specific and indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger

lets say we have 2 insert trigger named:trg1 and trg2 on same table and we want the trg1 to be fired first and than trg2 should be fired.
so to perform this we will have to set its order using sp_settriggerorder. otherwise their default ordering will be not sure as per required.

Create trigger:

trg1:

create trigger dbo.trg1 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 1'

trg2:

create trigger dbo.trg2 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 2'


Setting order of trigger:
exec sp_settriggerorder @triggername = 'trg1',
@order = 'first',
@stmttype = 'insert',
@namespace = null

exec sp_settriggerorder @triggername = 'trg2',
@order = 'last',
@stmttype = 'insert',
@namespace = null
go

Solutions By: Rajesh Rolen