SQL Triggers Order of Execution

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.
Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. Replication requires that its trigger be the first trigger. Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. If you try to make a trigger a first trigger after a table has been included in a subscription, sp_settriggerorder returns an error. If you use ALTER TRIGGER on the replication trigger, or use sp_settriggerorder to change the replication trigger to a Last or None trigger, the subscription does not function correctly.



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.



General Trigger Considerations:
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.
If the same trigger must 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 that statement type.



Example:


See sample script below:


--CONNECTING master DATABASE
USE master
go
--BY DEFAULT TRIGGERS ARE FIRED IN THE ORDER THEY ARE CREATED
set nocount on
go
--CREATED A TEMPORARY TABLE
create table dbo.customer (customerid int identity primary key)
go
--FIRST TRIGGER
create trigger dbo.tr_customer_1 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 1'
go
--SECOND TRIGGER
create trigger dbo.tr_customer_2 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 2'
go
--THIRD TRIGGER
create trigger dbo.tr_customer_3 on dbo.customer
for insert
as
set nocount on
print 'firing original trigger 3'
go
print 'Inserting row with default order of triggers'
go
--EXECUTED A SIMPLE INSERT QUERY
insert into dbo.customer default values
go

/*CHANGE TRIGGER ORDER*/
exec sp_settriggerorder @triggername = 'tr_customer_1',
@order = 'first',
@stmttype = 'insert',
@namespace = null

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

exec sp_settriggerorder @triggername = 'tr_customer_3',
@order = 'none',
@stmttype = 'insert',
@namespace = null
go
print 'Inserting row with modified order of triggers'
go
--AGAIN EXECUTED A SAMPLE QUERY
insert into dbo.customer default values
go
drop table dbo.customer
go