Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Setting Trigger Order
Published: Aug 27, 2017
This is something that comes up rarely, but when it does it can be a real pain.

I’ve seen situations in which you have multiple triggers on the same table and these Triggers could also be set to fire from the same action.

This can cause a lot of issues.

What happens if you want these Triggers to fire in a specific order? It might well be the case that if they fire in the wrong order then data could end up being incorrectly processed.

Actually there’s something in SQL Server which is there purely to deal with this exact situation.

SQL Server includes the option to set the order in which Triggers are fired.

So let’s do a quick (albeit absurd) demo:

use testDB

if object_id('prices') is not null drop table prices
if object_id('pricesLog') is not null drop table pricesLog

create table prices
priceID int identity,
orderNo int,
price decimal(19, 2)

create table pricesLog
priceID int,
price decimal(19, 2),
userName varchar(100),
timeInserted datetime

-- log the price and who made it

create trigger tr_priceLog
on prices
   insert into
select priceID, price, suser_sname(), current_timestamp
from inserted

-- Update to add tax

create trigger tr_priceTax
on prices
set p.price = p.price * 1.2
from prices p
join inserted i
on p.priceID = i.priceID

insert into prices
values (1, 100.00),
2, 956.48)

select *
from pricesLog
order by priceID

What we wanted was to log the gross value, but the triggers have fired in the wrong order and therefore we’re logging the net value:

So how can we change this without having to drop and re-create all triggers and doing so in a specific order? (No absolute guarantee that will work either)

exec sp_settriggerorder 'tr_priceTax', 'first', 'insert'
exec sp_settriggerorder 'tr_priceLog', 'last', 'insert'
-- can use order values:  'LAST', 'FIRST', 'NONE'

Running the whole code again, but including this gives us:

Therefore if you have business rules which require Triggers to be fired in a specific order then you can use sp_setTriggerOrder in order to achieve this.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.