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:
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,
price decimal(19, 2)
create table pricesLog
price decimal(19, 2),
-- log the price and who made it
create trigger tr_priceLog
insert into pricesLog
select priceID, price, suser_sname(), current_timestamp
-- Update to add tax
create trigger tr_priceTax
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),
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.