Execution Order of Triggers in SQL

Triggers are stored programs that are automatically executed or fired when a specified event occurs. It is a database object that is bound to a table and is executed automatically. We cannot call triggers explicitly. Triggers provide data integrity and are used to access and check data before and after modification using DDL or DML queries.

Triggers are used mainly in the following events:

1. Insert Data into table
2. Delete data from table
3. Update table record

We can create more than one trigger for the same event (in other words an INSERT, DELETE, UPDATE transaction). These are one problem, however. Triggers don't have a specified execution order. Execution of triggers is performed randomly. Sometimes the business logic dictates that we need to define two triggers on a table that must fire in a specific order on the same table action. For example when we insert rows in a table (INSERT statement) two triggers must fire and the second must fire after the first one for our logic to be implemented correctly.

SQL Server contains a sp_settriggerorder Stored Procedure for defining the execution orders of triggers.

Syntax

sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘  , [ @order = ] ‘value‘  , [ @stmttype = ] ‘statement_type‘  [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]