Setting Trigger Order
Published: 7th September 2015
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.
Comments:
NB: Comments will only appear once they have been moderated.