#0177 – SQL Server – Control the trigger execution order


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.

Advertisements

7 thoughts on “#0177 – SQL Server – Control the trigger execution order

  1. Olga Medvedeva

    Nakul, thank you for the post.

    But you are right it’s really bad practice to develop business logic solutions in accordance with order of firing triggers. Anyway you can set only first and last trigger to fire, but the order in which triggers between the first and last are fired is undefined.

    And you can not set order of firing INSTEAD OF triggers, only AFTER triggers.

    Like

    Reply
  2. Jeff Moden

    > While the long term solution was to
    > re-engineer the application to move
    > the required logic into a stored
    > procedure…

    I guess I’ll have to say “It Depends” on when you want the business logic to occur. While it’s fine to have included the logic into a stored procedure, that doesn’t guarantee that everyone will correctly include the logic into all stored procedures, ad hoc INSERTs/UPDATEs, or embedded SQL. The only way to guarantee such a thing is by using a trigger on a table.

    The fix that I would likely have implemented (and have done so on many occasions) would be to incorporate both required actions in the correct order into a single trigger.

    Like

    Reply
  3. Nakul Vachhrajani

    Thank-you, Olga and Jeff for your feedback! I really appreciate your taking the time out to read my post and sharing your views on it.

    @Olga: Agree, this works only for AFTER triggers, not INSTEAD OF and replication triggers.

    @Jeff: Yes, if the logic absolutely has to reside in a trigger, combining the multiple triggers into a single one is the way to go. On the other hand, if the team has started working in a way that all database access and manipulation happens with the help of stored procedures, then moving to stored procedures is the solution. Combining into a single trigger or moving over to stored procedures are just 2 different ways of working around the same issue, and which one to use depends entirely upon the situation at hand.

    Once again, I thank-you for sharing your views. I am honoured.

    Like

    Reply
  4. vmvadivel

    Nice Post. Btw as Jeff too pointed out I would have also prefered having a single trigger that does multiple activities (if order is important). I would be worried about maintainability of code in the long run.

    Like

    Reply
  5. Nakul Vachhrajani

    Thank-you, Vadivel. I agree – creating a single trigger or moving on to an SP (if possible) should be worked upon.

    One comes across such implementations when working on legacy applications. In my case, one team of developers had thought it best not to touch another (now non-existant) team’s implementation on account of lack of documentation and created their own, parallel universe before we took things up and implemented processes around the work to prevent these issues from happening, causing problems. Murphy’s law always holds true for such “patches” – they always surface in the middle of a deployment, threatening a roll-back.

    Sustenance is interesting – it teaches what one should not do 🙂

    Like

    Reply
  6. Nakul Vachhrajani

    @Lokeshkumarn: It is coming up 🙂 – in fact there are a full 10 parts to that tutorial. Hope you like them!

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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