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; GO EXEC sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order='First', @stmttype = 'UPDATE'; GO EXEC sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader2', @order='None', @stmttype = 'UPDATE'; GO EXEC sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='Last', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE'; GO
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.