How to force trigger order in SQL SERVER

Often when we develop an application and design the database we enforce business logic with the use of triggers.

Sometimes the business logic dictates that we need to define two triggers on a table which must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one, in order our logic to be implemented correctly.

Generally speaking multiple triggers on the same table for the same action are not fired in a predefined oreder. We can set the firing order for 2 AFTER triggers.We can do this by using the system stored procedure sp_settriggerorder. Do not try to do the same with INSTEAD OF triggers. You will receive an error.

I have taken the definition of sp_settriggerorder  from Books OnLine.

sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘
        , [ @order = ] ‘value‘
        , [ @stmttype = ] ‘statement_type‘
        [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ] 

A brief explanation of the arguments follows.

[ @triggername = ] ‘[ triggerschema. ] triggername‘

It is the name of the trigger and the schema to which it belongs.

[ @order = ] ‘value‘

Is the setting for the new order of the trigger. value is varchar(10) and it can be any one of the following values.

First – Trigger is fired first.

Last – Trigger is fired last.

None – Trigger is fired in undefined order.

[ @stmttype = ] ‘statement_type‘

Specifies the SQL statement that fires the trigger. statement_type is varchar(50) and can be INSERT, UPDATE, DELETE.

@namespace = { ‘DATABASE’ | ‘SERVER’ | NULL }

Specifies whether a DDL trigger was created on the database or on the server

In order to see an example, let’s create a table and set two INSERT triggers on that table that must fire in a specific order. Let’s call the table employee.

CREATE TABLE [dbo].[Employee](IDENTITY(1,1) NOT NULL

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

([EmployeeID] ASC))

Let’s create our triggers

Trigger number 1

create trigger dbo.tr_num_1 on dbo. employee

for insert

as
print ‘i am number 1′

go

 

Trigger number 2

create trigger dbo.tr_num_2 on dbo.employee

for insert

as

print ‘i am number 2′

go

when a new employee is inserted,tr_num_1 must be fired before tr_num_2.
we must insert some values to our table.

insert into employee default values
The triggers fire and we see that they fire in the desired order.
i am number 1
i am number 2
(1 row(s) affected)
Now if we drop the first trigger

DROP TRIGGER [dbo].[tr_num_1]
and create it again

create trigger [dbo].[tr_num_1] on [dbo].[Employee]
for insert
as
print ‘i am number 1′
GO

Now we try to insert a new row in the table.

insert into employee default values

i am number 2
i am number 1
(1 row(s) affected)

Now we have a problem. The second trigger fires first. Let’s fix that by using the sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ‘tr_num_1′,

@order=‘first’,

@stmttype= ‘insert’,
@namespace= null

exec sp_settriggerorder @triggername = ‘tr_num_2′,

@order= ‘last’,

@stmttype= ‘insert’,

@namespace = null

now if we enter a record in our table

insert into employee default values

we will see the following results

i am number 1
i am number 2
(1 row(s) affected)

The firing order is correct!!!!

About these ads