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
go
if object_id('prices') is not null drop table prices
if object_id('pricesLog') is not null drop table pricesLog
go
create table prices
(
priceID int identity,
orderNo int,
price decimal(19, 2)
)
go
create table pricesLog
(
priceID int,
price decimal(19, 2),
userName varchar(100),
timeInserted datetime
)
go
-- log the price and who made it
create trigger tr_priceLog
on prices
after insert
as
begin
insert into pricesLog
select priceID, price, suser_sname(), current_timestamp
from inserted
end
go
-- Update to add tax
create trigger tr_priceTax
on prices
after insert
as
begin
update p
set p.price = p.price * 1.2
from prices p
join inserted i
on p.priceID = i.priceID
end
go
insert into prices
values (1, 100.00),
(2, 956.48)
go
select *
from pricesLog
order by priceID
go
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'
go
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.