Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Forcing Trigger Firing Order in SQL Server

MSSQLTips author Armando Prato By:   |   Read Comments   |   Related Tips: More > Triggers

Problem
I have two triggers defined on my 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.  How can I make sure that they fire in the correct order to enforce my business logic?  In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?

Solution
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:

sp_settriggerorder Parameters

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 */

Here is an explanation of the parameters:

  • 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

Example 1 - Trigger Firing Order

Let's create a sample table called customer that has two insert triggers defined on it. The business rule is that when a new customer is inserted, trigger 1 must always fire before trigger 2.

Create the sample table

set nocount on
go
create table dbo.customer (customerid int identity primary key)
go

Create trigger 1

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

Create trigger 2

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

Sample insert statement

insert into dbo.customer default values
go

Overall code and output

As we can see, the triggers do in fact fire in the expected order.

Example  - Trigger Firing Order

Let's assume that a code change is required to trigger 1. Let's make the change and insert a new customer into the table.

Drop trigger 1

drop trigger dbo.tr_customer_1
go

Create trigger 1

create trigger dbo.tr_customer_1 on dbo.customer
for insert
as
set nocount on
print 'firing modified trigger 1'
go

Sample insert statement

insert into dbo.customer default values
go

Overall code and output

As we now see, our business rule has been violated. Trigger 2 fired before trigger 1. Using sp_settriggerorder, we can correct this condition

Corrective code

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
go

Sample insert statement

insert into dbo.customer default values
go

Overall code and output

The trigger firing order has been corrected.

 Firing More Than 3 Triggers

If you have more than 2 triggers, you can actually order all 3 if you specify the FIRST and the LAST trigger to fire. By default, any triggers that fire between the FIRST defined trigger and the LAST defined trigger are not fired in a specific order. As a result, a three trigger set up can be configured to fire in 1-2-3 order by declaring which trigger should fire FIRST and which trigger should fire LAST. If you have 4 or more triggers, you're out of luck. In these cases, the FIRST trigger will fire, the unordered triggers will fire in non-guaranteed order, and then finally, the LAST trigger will fire.

Trigger Caveats

There is a gotcha to watch out for: If you DROP the trigger and re-create it, or if you ALTER the trigger, the attribute assigned to the trigger is dropped and you will have to redefine it by re-executing sp_settriggerorder. Also, for replicated sites, SQL Server Replication will create a FIRST trigger on replicated tables. In this case, these triggers should not be changed otherwise you may end up with unpredictable replication results.

Alternative - OBJECTPROPERTY

Lastly, determining the firing attribute of a trigger can be done with the OBJECTPROPERTY function using one of the available properties such as ExecIsFirstInsertTrigger or ExecIsLastInsertTrigger. Read more about the trigger attribute options available with the OBJECTPROPERTY function in the SQL Server 2000 and 2005 Books Online.

Next Steps



Last Update: 1/10/2008


About the author
MSSQLTips author Armando Prato
Armando Prato has over 24 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.