How do you priortize multiple triggers of a table?

Question

I have a couple of triggers on a table that I want to keep separate and would like to priortize them.

I could have just one trigger and do the logic there, but I was wondering if there was an easier/logical way of accomplishing this of having it in a pre-defined order.

Answer 1

Use sp_settriggerorder. You can specify the first and last trigger to fire depending on the operation.

sp_settriggerorder on MSDN

From the above link:
A. Setting the firing order for a DML trigger
The following example specifies that trigger uSalesOrderHeader be the first trigger to fire after an UPDATE operation occurs on the Sales.SalesOrderHeader table.

USE AdventureWorks;
GO
sp_settriggerorder 
    @triggername= 'Sales.uSalesOrderHeader', 
    @order='First', 
    @stmttype = 'UPDATE';

B. Setting the firing order for a DDL trigger
The following example specifies that trigger ddlDatabaseTriggerLog be the first trigger to fire after an ALTER_TABLE event occurs in the AdventureWorks database.

USE AdventureWorks;
GO
sp_settriggerorder 
    @triggername= 'ddlDatabaseTriggerLog', 
    @order='First', 
    @stmttype = 'ALTER_TABLE', 
    @namespace = 'DATABASE';

Answer 2

See here.

Answer 3

You can use sp_settriggerorder to define the order of each trigger on a table.

However, I would argue that you'd be much better off having a single trigger that does multiple things. This is particularly so if the order is important, since that importance will not be very obvious if you have multiple triggers. Imagine someone trying to support the database months/years down the track. Of course there are likely to be cases where you need to have multiple triggers or it really is better design, but I'd start assuming you should have one and work from there.

Answer 4

Rememebr if you change the trigger order, someone else could come by later and rearrange it again. And where would you document what the trigger order should be so a maintenance developer knows not to mess with the order or things will break? If two trigger tasks definitely must be performed in a specific order, the only safe route is to put them in the same trigger.