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.
SQLTwins by Nakul Vachhrajani 
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.
LikeLike
> 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.
LikeLike
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.
LikeLike
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.
LikeLike
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 🙂
LikeLike
Nice Post. I was expecting Sql Profiler part 4 tutorial from you.
LikeLike
@Lokeshkumarn: It is coming up 🙂 – in fact there are a full 10 parts to that tutorial. Hope you like them!
LikeLike