jump to navigation

How to force trigger order in SQL SERVER June 1, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags: , ,
trackback

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!!!!

Comments»

1. How to force trigger order in SQL SERVER - June 1, 2008

[…] dhite wrote an interesting post today onHere’s a quick excerpt@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 … […]

2. Jackson Jarvis - September 16, 2011

–List tables with triggers and their firing order. By Jackson Jarvis.
SELECT [tbl].[name] AS ‘Table’
,[trg].[name] AS ‘Trigger’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsFirstInsertTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Insert First’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsLastInsertTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Insert Last’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsFirstUpdateTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Update First’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsLastUpdateTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Update Last’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsFirstDeleteTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Delete First’
,CASE OBJECTPROPERTY([trg].[id] ,’ExecIsLastDeleteTrigger’)
WHEN 0 THEN ”
ELSE ‘X’
END AS ‘Delete Last’
FROM [sysobjects] AS [trg] WITH (NOLOCK)
JOIN [sysobjects] AS [tbl] WITH (NOLOCK)
ON [trg].[parent_obj] = [tbl].[id]
WHERE [trg].[TYPE] IN (N’TR’)
ORDER BY
[tbl].[name] ASC
,[trg].[name] ASC;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: