Explain the difference between a row-level trigger (FOR EACH ROW) and a statement-level trigger (FOR EACH STATEMENT), including exactly how many times each fires when one UPDATE matches zero rows and when it matches a thousand rows.

middlemust knowfreq 50%

answer

row: once per row, zero rows = never
statement: exactly once, even at zero rows
BEFORE stmt / rows / AFTER stmt bracketing
row-level turns set DML into a loop
SQL Server = statement-level only; MySQL = row-level only

basics

~20 s

A row-level trigger fires once per affected row: zero times for a zero-row UPDATE, a thousand times for a thousand rows. A statement-level trigger fires once per statement regardless, including when zero rows match. Row-level sees individual old and new values; statement-level sees the change as a set, if at all.

solid answer

~50 s

FOR EACH ROW binds the trigger to rows: the engine invokes the body once for every row the statement changes, giving it that row's old and new images. A thousand-row UPDATE runs it a thousand times; a zero-row UPDATE runs it never — which surprises people who put logging in a row trigger and see nothing when the WHERE clause matched nothing.

FOR EACH STATEMENT binds it to the statement: exactly one invocation per statement execution, even if zero rows were affected. It has no per-row context by default; to see the changed data it needs transition tables, where the engine supports them.

The practical split: use row-level when you must act on individual values — shaping a row in BEFORE, or per-row derived writes. Use statement-level for anything expressible as a set operation, because one set-based statement massively outperforms N procedure invocations on bulk DML.

Multi-row DML gives you both: statement BEFORE, then the row triggers around each row write, then statement AFTER.

`FOR EACH ROW` binds the trigger to rows: the engine invokes the body once for every row the statement changes, giving it that row's old and new images. A thousand-row UPDATE runs it a thousand times; a zero-row UPDATE runs it **never** — which surprises people who put logging in a row trigger and see nothing when the WHERE clause matched nothing. `FOR EACH STATEMENT` binds it to the statement: exactly one invocation per statement execution, **even if zero rows were affected**. It has no per-row context by default; to see the changed data it needs transition tables, where the engine supports them. The practical split: use row-level when you must act on individual values — shaping a row in BEFORE, or per-row derived writes. Use statement-level for anything expressible as a set operation, because one set-based statement massively outperforms N procedure invocations on bulk DML. Multi-row DML gives you both: statement BEFORE, then the row triggers around each row write, then statement AFTER.

code

sql · 11 lines

sql

CREATE TRIGGER trg_orders_row
  AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION per_row_work();

CREATE TRIGGER trg_orders_stmt
  AFTER UPDATE ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION per_statement_work();

-- UPDATE orders SET status='X' WHERE 1=0;
--   per_row_work():       0 invocations
--   per_statement_work(): 1 invocation

go deeper

for a junior

Give the counts: once per row versus once per statement, and note zero rows means a row trigger never runs.

for a middle

Add the bracketing order for multi-row DML and the reason to prefer statement-level for bulk work.

for a senior

Tie the choice to throughput on large statements and to portability traps between engines that support only one level.

for a principal

Frame the level choice as a write-path budget decision, and require set-based statement-level implementations for anything on a hot table.

Two binding levels

Every trigger declares what it fires for. Standard SQL spells this FOR EACH ROW or FOR EACH STATEMENT, and the choice is orthogonal to BEFORE/AFTER timing — you can have a BEFORE STATEMENT trigger, an AFTER ROW trigger, and so on.

Row-level means: for each row this statement inserts, updates or deletes, run the body once, with access to that row's before and after images.

Statement-level means: run the body once for this statement execution, regardless of how many rows it touched.

The firing counts, precisely

Take UPDATE orders SET status = 'X' WHERE ...:

rows matched	FOR EACH ROW fires	FOR EACH STATEMENT fires
0	0 times	1 time (BEFORE), 1 time (AFTER)
1	1 time	1 + 1
1000	1000 times	1 + 1

The zero-row case is the one interviewers probe. A statement-level trigger fires because a statement executed; a row-level trigger has nothing to fire for. If you need "someone attempted this operation" — an access log, a rate check — that must be statement-level. If you need "this row changed", row-level.

A related subtlety: an UPDATE that sets a column to the value it already holds normally still counts as an affected row and still fires row triggers — the engine performs the write even though the value is unchanged. That is why change-detection logic inside row triggers must compare old and new explicitly rather than assume it only runs on real changes.

Ordering when both exist

For a single multi-row statement the sequence is:

BEFORE statement trigger — once, before any row is touched.
For each row: BEFORE row trigger, the row write, AFTER row trigger.
AFTER statement trigger — once, after all rows are done.

(Engines vary on whether AFTER row triggers are deferred to the end of the statement rather than interleaved; the observable guarantee is the bracketing, not the interleaving.)

That bracketing is what makes statement-level triggers natural for setup and teardown work: initialize a batch context in BEFORE STATEMENT, summarize in AFTER STATEMENT.

Performance: the reason this question is asked

A row-level trigger converts set-based DML into a per-row procedural loop. Each invocation pays context-switch overhead into the procedural runtime, plus whatever queries the body runs. On a million-row statement this is the difference between minutes and hours.

A statement-level trigger fires once and, given transition tables, can do the same work as a single INSERT ... SELECT over the changed set — the engine's optimizer processes it as a normal set operation. When people report that triggers made a batch job unusable, the fix is very often converting row-level auditing into statement-level auditing over transition tables.

When row-level is genuinely required

Modifying the row being written. Only a BEFORE row trigger has a mutable row image. Value shaping cannot be statement-level.
Skipping individual rows, where the engine supports it via a row trigger's return value.
Per-row logic that cannot be expressed as a set operation — genuinely rare; most "per row" logic is a join or an aggregate in disguise.
Engines without transition tables. If a statement-level trigger has no way to see which rows changed, row-level is the only option that carries data. This is why older Oracle code uses row triggers plus package-level collections, and why compound triggers exist.

Vendor notes

SQL Server effectively has only statement-level triggers; the inserted and deleted pseudo-tables give the whole changed set, and per-row behavior must be written as set operations or an explicit cursor. Code that assumes SQL Server triggers fire once per row is a well-known bug source.
PostgreSQL supports both levels, with transition tables for statement-level triggers.
Oracle supports both, plus compound triggers that bundle statement-level and row-level sections in one object to share state.
MySQL supports row-level triggers only.

The interview signal

Give the firing counts including the zero-row case without hedging, state the bracketing order for multi-row DML, and then connect the choice to performance: prefer statement-level with transition tables unless you specifically need per-row mutation.

## Two binding levels Every trigger declares what it fires *for*. Standard SQL spells this `FOR EACH ROW` or `FOR EACH STATEMENT`, and the choice is orthogonal to BEFORE/AFTER timing — you can have a BEFORE STATEMENT trigger, an AFTER ROW trigger, and so on. **Row-level** means: for each row this statement inserts, updates or deletes, run the body once, with access to that row's before and after images. **Statement-level** means: run the body once for this statement execution, regardless of how many rows it touched. ## The firing counts, precisely Take `UPDATE orders SET status = 'X' WHERE ...`: | rows matched | FOR EACH ROW fires | FOR EACH STATEMENT fires | |---|---|---| | 0 | 0 times | 1 time (BEFORE), 1 time (AFTER) | | 1 | 1 time | 1 + 1 | | 1000 | 1000 times | 1 + 1 | The zero-row case is the one interviewers probe. A statement-level trigger fires because a statement executed; a row-level trigger has nothing to fire *for*. If you need "someone attempted this operation" — an access log, a rate check — that must be statement-level. If you need "this row changed", row-level. A related subtlety: an UPDATE that sets a column to the value it already holds normally still counts as an affected row and still fires row triggers — the engine performs the write even though the value is unchanged. That is why change-detection logic inside row triggers must compare old and new explicitly rather than assume it only runs on real changes. ## Ordering when both exist For a single multi-row statement the sequence is: 1. BEFORE statement trigger — once, before any row is touched. 2. For each row: BEFORE row trigger, the row write, AFTER row trigger. 3. AFTER statement trigger — once, after all rows are done. (Engines vary on whether AFTER row triggers are deferred to the end of the statement rather than interleaved; the observable guarantee is the bracketing, not the interleaving.) That bracketing is what makes statement-level triggers natural for setup and teardown work: initialize a batch context in BEFORE STATEMENT, summarize in AFTER STATEMENT. ## Performance: the reason this question is asked A row-level trigger converts set-based DML into a per-row procedural loop. Each invocation pays context-switch overhead into the procedural runtime, plus whatever queries the body runs. On a million-row statement this is the difference between minutes and hours. A statement-level trigger fires once and, given transition tables, can do the same work as a single `INSERT ... SELECT` over the changed set — the engine's optimizer processes it as a normal set operation. When people report that triggers made a batch job unusable, the fix is very often converting row-level auditing into statement-level auditing over transition tables. ## When row-level is genuinely required - **Modifying the row being written.** Only a BEFORE *row* trigger has a mutable row image. Value shaping cannot be statement-level. - **Skipping individual rows**, where the engine supports it via a row trigger's return value. - **Per-row logic that cannot be expressed as a set operation** — genuinely rare; most "per row" logic is a join or an aggregate in disguise. - **Engines without transition tables.** If a statement-level trigger has no way to see which rows changed, row-level is the only option that carries data. This is why older Oracle code uses row triggers plus package-level collections, and why compound triggers exist. ## Vendor notes - **SQL Server** effectively has only statement-level triggers; the `inserted` and `deleted` pseudo-tables give the whole changed set, and per-row behavior must be written as set operations or an explicit cursor. Code that assumes SQL Server triggers fire once per row is a well-known bug source. - **PostgreSQL** supports both levels, with transition tables for statement-level triggers. - **Oracle** supports both, plus compound triggers that bundle statement-level and row-level sections in one object to share state. - **MySQL** supports row-level triggers only. ## The interview signal Give the firing counts including the zero-row case without hedging, state the bracketing order for multi-row DML, and then connect the choice to performance: prefer statement-level with transition tables unless you specifically need per-row mutation.

You want to log every attempt to delete from a table, even attempts that match no rows. Which trigger level do you use and why?
A statement-level trigger, because it fires once per statement execution regardless of the affected row count, including zero. A row-level trigger would never fire for a no-match DELETE, so the attempt would go unrecorded. If you also need the deleted rows themselves, read them from the transition table in the same statement-level trigger.
Why is trigger code written for SQL Server a common source of bugs when ported to an engine with row-level triggers, and vice versa?
SQL Server triggers fire once per statement with the whole changed set in the inserted/deleted pseudo-tables, so logic written there assumes set semantics. Ported to a row-level engine it may run once per row and repeat work N times. In the other direction, per-row logic ported to SQL Server silently processes only one arbitrary row if it assumes a single-row pseudo-table — a classic multi-row-update data-corruption bug.

saying these in an interview costs you the question

Saying a row-level trigger still fires once when the statement matches zero rows
Saying a statement-level trigger does not fire when zero rows are affected
Assuming SQL Server triggers fire per row
Believing an UPDATE that writes an identical value skips row-trigger firing
Thinking statement-level triggers can modify the rows being written

Explain the difference between a row-level trigger (FOR EACH ROW) and a statement-level trigger (FOR EACH STATEMENT), including exactly how many times each fires when one UPDATE matches zero rows and when it matches a thousand rows.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Two binding levels

The firing counts, precisely

Ordering when both exist

Performance: the reason this question is asked

When row-level is genuinely required

Vendor notes

The interview signal

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

Two binding levels

The firing counts, precisely

Ordering when both exist

Performance: the reason this question is asked

When row-level is genuinely required

Vendor notes

The interview signal

answer

go deeper

context