skip to content

Trigger Pitfalls

18 roadmaps5 questions

I learn why teams grow wary of triggers: unpredictable ordering, cascading and recursive firing, and side effects invisible to the application developer. Interviewers ask for these pitfalls to see if I have production scars and know the alternatives for audit and derived data.

part ofRelational database conceptsoverview, primer and where to startread it →
on this pageshow

explore

questions

5

A nightly job runs a single UPDATE that touches five million rows and now takes hours; the same UPDATE against a structurally identical table with no triggers finishes in minutes. Explain concretely how row-level triggers produce that cost, and what you would change.

level: seniorimportance: must knowfreq 48%

answer

  1. FOR EACH ROW = N procedure calls
  2. N+1 hidden inside the database
  3. audit row doubles log + index writes
  4. one long transaction: locks, bloat, replica lag
  5. rewrite as statement-level over transition tables

basics

~20 s

A row-level trigger runs once per affected row, so a set-based update becomes five million procedure invocations, often each with its own query. It also multiplies written bytes, log volume and locks, all inside one long transaction. Fix it with statement-level triggers over transition tables, batching, or doing the derived work in the statement itself.

solid answer

~60 s

Three costs stack up. **Per-row invocation.** `FOR EACH ROW` means the trigger body executes five million times. Even an empty body costs a context switch into the procedural engine; a body that runs a lookup turns the statement into an N+1 pattern executed inside the database, invisible to the application's query logs. **Write amplification.** If the trigger inserts an audit row per change, you have written ten million rows, not five million, plus their index maintenance and their transaction-log records. Log volume can more than double, which also slows replication and inflates backups. **Transaction footprint.** Everything runs in the invoking transaction, so locks and old row versions accumulate for the whole run, blocking others and bloating storage. Fixes in order of preference: move the logic to a **statement-level trigger reading transition tables** so it becomes one set-based statement; batch the update into bounded chunks; compute derived values in the UPDATE itself or with a generated column; or, for a controlled maintenance window, disable the trigger and do its work as an explicit set-based step.

code

sql · 16 lines
sql
-- Before: fires 5,000,000 times
CREATE TRIGGER audit_row
  AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION write_audit_row();

-- After: fires once per statement, one INSERT ... SELECT
CREATE TRIGGER audit_stmt
  AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION write_audit_set();

-- inside write_audit_set():
-- INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
-- SELECT n.id, o.status, n.status, CURRENT_TIMESTAMP
-- FROM new_rows n JOIN old_rows o ON o.id = n.id
-- WHERE o.status IS DISTINCT FROM n.status;

go deeper

for a junior

Say clearly that a row-level trigger runs once per row, so five million rows means five million executions.

for a middle

Add the second-order costs: extra queries inside the body, extra rows and index maintenance, and a much larger transaction.

for a senior

Diagnose with a triggerless comparison and log-volume evidence, then propose the statement-level transition-table rewrite and bounded batching.

for a principal

Weigh the write-path budget overall — replication lag, backup size, lock-hold windows — and decide whether the derived work belongs in the write path at all versus log-based capture.

## Why per-row is the whole story SQL engines are built to process sets: one UPDATE with a good access path streams rows through a tight loop of page reads and writes. A row-level trigger breaks that. `FOR EACH ROW` tells the engine to suspend set processing on every qualifying row, enter the procedural language runtime, execute a block of code, and return. Five million rows means five million entries into that runtime. Even a trivial body has fixed overhead per invocation: building the old and new row images, setting up the execution context, evaluating the WHEN condition if any. Measured in microseconds it looks free; multiplied by five million it is tens of minutes before the body does anything useful. ## The costs that actually dominate **Queries inside the body.** The common pattern is a trigger that selects the parent row and then inserts an audit record. That is one or two extra statements per row — an N+1 problem, except it lives in the database and never appears in application-side query metrics. Ten million extra statements at even 0.2 ms each is over half an hour of pure execution. **Write amplification.** Audit or history triggers double the rows written. Each audit row also maintains that table's indexes and produces its own transaction-log records (WAL, redo, or binlog depending on engine). Log volume is the quantity that hurts most widely: it is fsynced, shipped to replicas, and captured by backups, so a trigger that doubles it doubles replication lag and backup size for everyone, not just this job. **Transaction footprint.** Because triggers run in the invoking transaction, the run holds five million row locks (or their equivalent) from the first row to commit, and the engine must retain old row versions or undo information for the whole duration. Concurrent readers and writers queue behind it; on MVCC engines the table and its indexes bloat, and cleanup is deferred until after commit. A multi-hour transaction is itself a production hazard independent of CPU cost. **Plan opacity.** The statement's execution plan does not show trigger work as part of the scan; the engine reports it separately or not at all. Engineers stare at a plan that looks fine while the wall-clock time comes from somewhere the plan never mentions. That is exactly why "identical table without triggers is minutes" is the decisive experiment. ## What to change, in order 1. **Rewrite as a statement-level trigger over transition tables.** Standard SQL exposes the set of changed rows to a `FOR EACH STATEMENT` trigger as transition tables (`REFERENCING OLD TABLE` / `NEW TABLE`); SQL Server has always exposed the same idea as `inserted` and `deleted`. One `INSERT ... SELECT` from the transition table replaces five million procedure calls with a single set-based statement. This is usually a 10x-100x improvement and preserves the semantics of "every change is recorded". 2. **Batch the DML.** Chunk the update into, say, 50k-row transactions in a loop keyed on the primary key. Per-row trigger cost remains, but lock hold time, log pressure, version retention and rollback risk all become bounded, and the job becomes restartable. 3. **Delete the trigger's reason to exist.** If it is deriving a column, a generated or computed column, or an expression in the UPDATE itself, does the job with no procedural call. If it is auditing, consider log-based change data capture, which reads the transaction log the engine already wrote and adds nothing to the write path. 4. **Disable for controlled maintenance.** For a one-off migration or a nightly job you fully control, disabling the trigger and performing its effect as one explicit set-based statement afterwards is legitimate — provided the job is the only writer for that window and the script re-enables it even on failure. Do not make this the routine answer: it is exactly how audit gaps get created. ## The interview signal Name the per-row invocation cost, then go further — the queries inside the body, the doubled log volume and its effect on replication, and the transaction that holds locks for hours. Then propose the statement-level transition-table rewrite as the first fix rather than jumping straight to "disable the trigger".

  • If you disable the trigger to run the bulk job, what must you guarantee?
    That no other session writes the table during the window, that the trigger's effect is reproduced by an explicit set-based statement in the same script, and that re-enabling happens even if the job fails, so wrap it in error handling. Also note that disabling is a schema-level change visible to every session, not a session-local setting, so it is unsafe on a live multi-writer table.
  • How would you prove that triggers, and not the UPDATE itself, are the bottleneck?
    Run the same UPDATE against a copy of the table without the triggers, on the same hardware and data volume, and compare wall-clock time. Corroborate with transaction-log bytes generated per run and with the row counts written to the tables the trigger maintains; if log volume is roughly double the rows the application touched, the trigger's writes are the amplifier.

saying these in an interview costs you the question

  • Assuming a row-level trigger fires once per statement rather than once per row
  • Expecting the execution plan of the UPDATE to reveal trigger cost
  • Proposing 'disable the trigger' as the first fix on a live multi-writer table
  • Ignoring that audit writes double transaction-log volume and therefore replication lag
  • Thinking the work commits incrementally rather than sitting in one long transaction

context

resolving links…

open as a page

A trigger on table A writes a row into table B, and a trigger on table B writes back into table A. Walk through what happens when an application inserts one row into A, and explain how relational engines limit cascading or recursive trigger firing and how you would design around it.

level: seniorimportance: must knowfreq 50%

basics

~20 s

Each trigger's own DML fires more triggers, so A to B to A can loop. Engines stop it with a nesting-depth cap or by disabling self-recursion by default, and the loop aborts the whole transaction. Fix it with change guards, idempotent writes, or by moving the second hop into application code.

open as a page

Why do teams often say that database triggers make an application harder to debug and maintain, and what practices reduce that pain when you decide to keep them?

level: juniorimportance: should knowfreq 40%

basics

~20 s

Triggers run invisibly: the application code shows one INSERT, but rows change in tables nobody named, and the logic lives outside the application repository. That breaks the trail from symptom to cause. Keeping them manageable means versioning them like code, testing them, and making them discoverable.

open as a page

A table has three separate AFTER INSERT row-level triggers on it, written by three different teams. What determines the order in which they execute, and why do experienced engineers avoid writing triggers whose correctness depends on that order?

level: middleimportance: should knowfreq 42%

basics

~20 s

Order is engine-defined, not portable: some engines order by trigger name, some by creation time, some let you pin only a first and last trigger. Renames, re-creations and dump restores silently change it, so keep triggers order-independent.

open as a page

You need a durable audit trail of every change to a handful of core tables. Compare building it with audit triggers, with log-based change data capture that reads the database transaction log, and with a transactional outbox table written by the application — and say when you would choose each.

level: principalimportance: should knowfreq 38%

basics

~20 s

Triggers capture every writer atomically with the change but tax every write and roughly double log volume. Log-based capture reads the transaction log the engine already writes, so the write path pays nothing, but it is asynchronous and loses application context. An outbox gives typed business events in the same transaction, but only for writers that cooperate.

open as a page