SQL Server Setting Triggers Firing Order

Estimated study time: 5 minutes. Control which trigger runs first, last, or in between.

When a table has more than one AFTER trigger for the same event, SQL Server doesn't guarantee a predictable execution order by default. If your logic depends on one trigger running before another, you need to explicitly set the firing order.

The Problem

By default, SQL Server only lets you mark one trigger as "first" and one as "last" — everything else runs in an undefined order in between.

Using sp_settriggerorder

EXEC sp_settriggerorder
  @triggername = 'trg_AuditLog',
  @order = 'First',
  @stmttype = 'INSERT';

EXEC sp_settriggerorder
  @triggername = 'trg_SendNotification',
  @order = 'Last',
  @stmttype = 'INSERT';

Parameters Explained

@triggername — the trigger you want to reorder
@order — accepts 'First', 'Last', or 'None'
@stmttype — the statement type: INSERT, UPDATE, or DELETE

Checking the Current Order

SELECT name, OBJECTPROPERTY(object_id, 'ExecIsFirstInsertTrigger') AS is_first
FROM sys.triggers
WHERE parent_id = OBJECT_ID('employees');

💡 Tip: Only one trigger can be "First" and one can be "Last" per table, per action — every other trigger sits in between with no guaranteed order among them.

Why This Matters