sql server trigger execution order

sql server set trigger order
sp_settriggerorder in sql server
sp_settriggerorder example
exec sp_settriggerorder

In this video we will discuss how to set the execution order of triggers using sp_settriggerorder stored procedure.

Server scoped triggers will always fire before any of the database scoped triggers. This execution order cannot be changed.

In the example below, we have a database-scoped and a server-scoped trigger handling the same event (CREATE_TABLE). When you create a table, notice that server-scoped trigger is always fired before the database-scoped trigger.

CREATE TRIGGER tr_DatabaseScopeTrigger
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
Print ‘Database Scope Trigger’
END
GO

CREATE TRIGGER tr_ServerScopeTrigger
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
Print ‘Server Scope Trigger’
END
GO

Using the sp_settriggerorder stored procedure, you can set the execution order of server-scoped or database-scoped triggers.

sp_settriggerorder stored procedure has 4 parameters
@triggername – Name of the trigger
@order – value can be First, Last or None. When set to None, trigger is fired in random order
@stmttype – SQL statement that fires the trigger. Can be INSERT, UPDATE, DELETE or any DDL event
@namespace – Scope of the trigger. Value can be DATABASE, SERVER, or NULL

EXEC sp_settriggerorder
@triggername = ‘tr_DatabaseScopeTrigger1’,
@order = ‘none’,
@stmttype = ‘CREATE_TABLE’,
@namespace = ‘DATABASE’
GO

If you have a database-scoped and a server-scoped trigger handling the same event, and if you have set the execution order at both the levels. Here is the execution order of the triggers.
1. The server-scope trigger marked First
2. Other server-scope triggers
3. The server-scope trigger marked Last
4. The database-scope trigger marked First
5. Other database-scope triggers
6. The database-scope trigger marked Last

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-trigger-execution-order.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/sql-server-trigger-execution-order_11.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

Full SQL Server Course

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists