It is generally a bad practice to have execution sequence dependent business logic inside of triggers. They should be inside stored procedures, and not triggers. But, a lot of us maintain databases designed & developed by external teams – members of which may not be with the organization anymore. What I am sharing today comes from a Sunday afternoon long time ago from when I was on-call during a customer on-premise deployment.

Building the Scenario

The application I was working on used triggers which had custom logic embedded into them. Apparently, one of the areas required that a record in another table be updated by an update trigger before another trigger to send out a notification of the change to an external system is executed – and that was the source of our problems.

It is difficult to recreate the scenario because the trigger order is by default, controlled by the SQL Server database engine. However, here goes – the script below creates a table with two very simple insert triggers on it.

USE tempdb
GO

--Safety Check
IF OBJECT_ID('triggerSequenceTest') IS NOT NULL
BEGIN
    DROP TABLE triggerSequenceTest
END
GO

CREATE TABLE triggerSequenceTest (RowId INT IDENTITY(1,1),
                                   RowValue NVARCHAR(20)
                                  )
GO

CREATE TRIGGER dbo.trig_UpdateTrig01
ON dbo.triggerSequenceTest
FOR INSERT
AS 
BEGIN
    DECLARE @insertedValue NVARCHAR(20)
    
    SELECT @insertedValue = inserted.RowValue from inserted
    
    PRINT 'Trigger 01: ' + @insertedValue
END
GO

CREATE TRIGGER dbo.trig_UpdateTrig02
ON dbo.triggerSequenceTest
FOR INSERT
AS 
BEGIN
    DECLARE @insertedValue NVARCHAR(20)
    
    SELECT @insertedValue = inserted.RowValue from inserted
    
    PRINT 'Trigger 02: ' + @insertedValue
END
GO

--Perform the INSERT
INSERT INTO triggerSequenceTest (RowValue) VALUES ('Microsoft')

The output that we (normally) receive is:

Trigger 01: Microsoft

Trigger 02: Microsoft

In my case, the trigger 02 was the replication trigger and was designed such that it was expecting a change made by trigger 01 to be in place. However, the trigger 02 was executed before trigger 01 causing all sorts of weird behavioural issues within the application and failures being logged into integration logs.

The Solution

In the very rare case when SQL Server fails to set the correct trigger order, one can control the execution to a certain extent. We can control which trigger needs to be executed first, and which one the last through a system stored procedure – sp_settriggerorder.

While the long term solution was to re-engineer the application to move the required logic into a stored procedure, the solution that we ended up using that day was forcibly setting the trigger order for the integration related trigger to last. As a demo, let’s move the trigger 01 to the last in the execution sequence:

--Change the trigger order
USE tempdb;
GO
sp_settriggerorder @triggername= 'dbo.trig_UpdateTrig01', @order='Last', @stmttype = 'INSERT';
GO

An attempt to insert some data now yields:

--Perform the INSERT again
INSERT INTO triggerSequenceTest (RowValue) VALUES ('SQL Server')

Trigger 02: Microsoft

Trigger 01: Microsoft

IMPORTANT: One of the key points to remember is that server scoped triggers are always executed first.

Reference: http://msdn.microsoft.com/en-us/library/ms186762.aspx

Until we meet next time,

Be courteous. Drive responsibly.