Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

sql server trigger execution order

Suggested Videos
Part 91 - Cross apply and outer apply in sql server
Part 92 - DDL Triggers in sql server
Part 93 - Server-scoped ddl triggers



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
Parameter Description
@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

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.