Microsoft SQL Server allows you to add multiple triggers on a specific table. This is a great feature of Microsoft SQL Server, however one of the issues that can catch people out is in the order that the triggers will ‘fire’. By default, the triggers will run in the order that they were created/added to the database (typically, but not guaranteed). SQL Server does provide a way to set the order of ‘AFTER’ triggers (to an extent), but it must be done using T-SQL statements as opposed to using GUI tools in Management Studio.
Microsoft SQL Server only allows you to set which triggers fires first, and which fires last. This is great if you only have 3 or fewer triggers on a table, but if you have more than 3, you will end up with 2 or more in the middle part, which will end up always running in an undefined order (typically in the order that they were created, but there are no guarantees).
The ‘sp_settriggerorder’ stored procedure allows the setting of the trigger order.
The syntax for this stored procedure is:
sp_settriggerorder 'trigger_name', 'order', 'trigger_type'
| Parameter | Description |
| trigger_name | The name of the trigger that you want to set the order for. |
| order | The order that you want to set it as. The valid options are ‘first’ or ‘last’ (or ‘none’ to clear the ordering). |
| trigger_type | The statement type of trigger, eg ‘insert’, ‘update’, or ‘delete’. |
An example of setting the ‘auditCustomers’ trigger to fire first:
sp_settriggerorder 'auditCustomers', 'first', 'insert'
An example of setting the ‘CopyAddressToInvoice’ trigger to fire last:
sp_settriggerorder 'CopyAddressToInvoice', 'last', 'insert'
Keep in mind that if you ever drop and recreate a trigger, the ordering information will get deleted, and you will need to run the ‘sp_settriggerorder’ statement again.
