Setting Trigger Order

Leave a comment

August 26, 2019 by Kenneth Fisher

If you can help it you probably shouldn’t be using triggers. But if you have to use a trigger you definitely don’t want to use more than one. That said, if you are going to be using more than one trigger it might be important to know what order they are going to be running in.

Note: Sometimes triggers are the answer to a problem, and sometimes you have to have more than one. And if you do have more than one sometimes you need to have them run in a specific order, sometimes you don’t. This post is just for the cases when you do need that specific order.

The first thing to remember is that you can have AFTER and INSTEAD OF triggers. INSTEAD OF triggers run instead of the event (duh). An example of an event might be an INSERT or an ALTER_TABLE. AFTER triggers run after the event (again duh). This should lead you an obvious conclusion. INSTEAD OF triggers run first. Also kind of obvious if you think about it, you can only have one INSTEAD OF trigger. So the only ones we have to worry about order are the AFTER triggers.

Unfortunately, there isn’t a 100% option for setting order. You’ve got three spots. First, last, and everything in between. There is no reasonable limit to the number of AFTER triggers you can have (you can have 2,147,483,647 objects in a database including triggers). That’s a lot of potential triggers. (Don’t, just don’t. Yes you. You know who I’m talking to.) To set the order of a trigger use the function sp_settriggerorder. (Examples taken, slightly modified, from BOL)

USE AdventureWorks2012;  
EXEC sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order='First', @stmttype = 'UPDATE';
EXEC sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader2', @order='None', @stmttype = 'UPDATE';
EXEC sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='Last', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';

Remember, that even though you can specify the order (of some) of your triggers you really want to be careful here. The time spent for each trigger to execute is added to the time spent to execute the event. So if you have three after triggers on an UPDATE statement then for each UPDATE statement to complete it has to wait on each of those three triggers to complete.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: