Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Triggers to Execute in Pre Defined Order

By Dinesh Asanka,

Introduction

This is a real world problem that I came across. I have two triggers which need to fire in a predefined order, which means Trigger A has to run first and after that Trigger B should execute. You may be curious why you can’t have a one trigger which has the Trigger A and Trigger B in one Trigger AB. Good point. Unfortunately, Trigger A is For Replication while the later trigger is not for replication, which defends the existence of two triggers.

Let us create a test environment for this. We need two tables for this. One is to write and test triggers and the other table is to log the trigger with its executed time.

CREATE TABLE [Trigger Priority] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[First] [int] NULL ,
[Second] [int] NULL ,
[Last] [int] NULL ,
[Status] [char] (1) NULL 
) ON [PRIMARY]
GO
Our target should be one insert trigger to update First column with some random number. This trigger will be trg_UpdateFirst

CREATE TRIGGER trg_UpdateFirst ON dbo.[Trigger Priority] 
FOR INSERT 
AS
declare @id int, @val as float
Select @id = id from inserted
select @val = floor(rand() * 10)
Update [Trigger Priority] set [First] = @val Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateFirst')
The last line of the trigger is to log the trigger name and its time in the TriggerLog table. The next trigger is to update Second column with the value of First Column.
CREATE TRIGGER trg_UpdateSecond ON dbo.[Trigger Priority] 
FOR INSERT 
AS
declare @id int
Select @id = id from inserted
Update [Trigger Priority] set [Second] = [First] Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateSecond')
The last trigger is to update the Last column with the summation of the First and Second columns.
CREATE TRIGGER trg_UpdateLast ON dbo.[Trigger Priority] 
FOR INSERT 
AS
declare @id int
Select @id = id from inserted
Update [Trigger Priority] set [Last] = [First] + [Second] Where ID = @id
Insert into TriggerLog (TriggerName) values ('trg_UpdateLast')
Now to get the expected results trg_UpdateFisrt, trg_UpdateSecond, and trg_UpdateLast triggers should execute in the above written order. What is your thought on this? What will be the order? Is it in random or in some particular order?

Before answering to above question let us see what has happened. After inserting a single record to the [Trigger Priority] table, first column is five which is ok and second field is 5 which is ok, too. However the last column is NULL! What has happened? Shouldn’t it be 10?

Now let us check the TriggerLog table. The order of the columns is trg_UpdateLast, trg_UpdateFirst and trg_UpdateSecond. After doing bit of research it was discovered that triggers are executed in the order which they were created. So ideally triggers should be created in the order of trg_UpdateFirst, trg_UpdateSecond and trg_UpdateLast. This is by no means an easy task as we all have experienced dynamic changes in the development process, which is out of control from the developers for most of the time.

Another question. At later stage how are you going to find out the order of the triggers?

select * from sysobjects where xType ='TR' order by id
From the above query you can identify the order in which triggers will be executed.

Set the Order

Now the question is how we can set the executing order. There is a system stored procedure, which is defined to support the above cause. That stored procedure is sp_settriggerorder. In this SP there are three parameters.

sp_settriggerorder[@triggername = ] 'triggername' 
, [@order = ] 'value' 
, [@stmttype = ] 'statement_type'
The first parameter is trigger name and the second parameter is the order. This order can take three values: "First", "None", and "Last". The last parameter is type of the trigger, whether it is Insert, Update or Delete. This indicates that you cannot afford to have four or five triggers with the same type to execute in a defined order. However, this will not be practical. However I have not come across that many triggers yet in a table.

This option cannot be set from the Alter Trigger or Create Trigger option. If an Alter Trigger statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset with sp_settriggerorder.

Permissions

The owner of the trigger and the table on which the trigger is defined has execution permissions for sp_settriggerorder. Members of db_owner and db_ddladmin roles in the current database, as well as the sysadmin server role can execute this stored procedure.

Retrieve the Order

Next issue is, at the later stage how you can get the execution order of the triggers. If I am not mistaken, there is no direct way of getting this information from the SQL Server enterprise manager. Instead, you write simple queries.

select objectproperty(object_id(' trg_UpdateFirst '), 'ExecIsFirstInsertTrigger') will give whether the trg_UpdateFirst is First Insert trigger?

Cautions

When you generate a script for the triggers, the priority will not scripted, which means that you need to re-run priority scripts. This is a draw back of the priority triggers.

SQL Server 2005

In SQL Server 2005 there is an additional parameter for the sp_settriggerorder, which is to say whether trigger is database trigger or server trigger. This is because in SQL Server 2005, you can write DDL triggers for SQL Servers as well.

Conclusion

Setting a priority order for a SQL Server trigger is not difficult. However, you need to take extra care when adopting this feature in your development structure. It will be good if Microsoft can provide solutions for the some if the issues raised earlier
 

Total article views: 10884 | Views in the last 30 days: 7
 
Related Articles
FORUM

Doubt About Insert Trigger?

Doubt About Insert Trigger?

FORUM

Trigger after Insert?

Trigger

FORUM

Bulk Insert and Triggers

Bulk Insert and Triggers

FORUM

Insert trigger not working when update trigger enabled

Update interfering with insert trigger.

FORUM

not updating inserted row in an insert trigger

not updating inserted row in an insert trigger

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones