Forcing Trigger Firing Order in SQL Server

By:   |   Comments (1)   |   Related: > 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

image002

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

image004

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

image006

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, November 20, 2015 - 3:31:55 AM - Khaled Back To Top (39109)

Thanks Armando

Very helpful tip















get free sql tips
agree to terms