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