Ordering trigger

If there are multiple triggers to be fired in a same table, SQL Server has a provision to order them. Mostly this is used to prioritize them.

To achieve this we have to use the system trigger sp_settriggerorder. This should not be used for INSTEAD OF triggers (Only with AFTER).

exec sp_settriggerorder @triggername = , @order = [FIRST|LAST|NONE],@stmttype = [INSERT|UPDATE|DELETE|], @namespace = [DATABASE|SERVER|NULL] 

Here stmttype is the type of the trigger.

Example:

Create a table to have two triggers first.

create table dbo.tblemployee (empid int identity primary key, empname varchar(100))

create trigger dbo.tr_employee_1 on dbo. tblemployee
for insert
as
set nocount on
print 'trigger1 started'
go

create trigger dbo.tr_ employee _2 on dbo. tblemployee
for insert
as
set nocount on
print 'trigger2 started'
go

Execute above two triggers in the table.

insert into dbo.tblemployee(empid, empname) values(1,'John')

By default the message has the following values

trigger1 started'

trigger2 started'

to change the order of the triggers, the trigger sp_settriggerorder needs to be called

exec sp_settriggerorder @triggername = 'tr_employee_1', 
@order = 'first', 
@stmttype = 'insert',
@namespace = null

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