Inside a trigger body, how do you reference the row's values before and after the change, and how does that differ between row-level and statement-level triggers? Explain what transition tables (REFERENCING OLD TABLE / NEW TABLE) give you.

seniorshould knowfreq 36%

answer

OLD/NEW = row shape; transition tables = set shape
INSERT: NEW only; DELETE: OLD only; UPDATE: both
NEW writable only in BEFORE row
read-only, statement-scoped relations
SQL Server inserted/deleted = same idea

basics

~20 s

Row-level triggers get single-row images, conventionally OLD and NEW: INSERT has only NEW, DELETE only OLD, UPDATE both. Statement-level triggers have no per-row image; transition tables expose the whole set of changed rows as two readable relations you can join and aggregate in one set-based statement.

solid answer

~50 s

Row-level. The body sees two row-shaped values, standardly named OLD and NEW. INSERT populates only NEW, DELETE only OLD, UPDATE both, so referencing the wrong one for the event is an error or a null. Only in a BEFORE row trigger is NEW writable — assigning there changes what gets stored.

Statement-level. It fires once and has no single row to describe. Standard SQL solves this with transition tables, declared via REFERENCING OLD TABLE AS ... NEW TABLE AS ...: read-only, statement-scoped relations holding every row the statement removed and every row it produced. You can SELECT from them, join them on the key, aggregate them, and drive a single INSERT ... SELECT.

That is the performance lever: auditing a million-row UPDATE becomes one set-based statement rather than a million procedure calls.

Support varies: SQL Server always exposed this as the inserted and deleted pseudo-tables, PostgreSQL added transition tables in version 10, Oracle offers compound triggers as its workaround, MySQL has row-level triggers only.

**Row-level.** The body sees two row-shaped values, standardly named OLD and NEW. INSERT populates only NEW, DELETE only OLD, UPDATE both, so referencing the wrong one for the event is an error or a null. Only in a BEFORE row trigger is NEW writable — assigning there changes what gets stored. **Statement-level.** It fires once and has no single row to describe. Standard SQL solves this with **transition tables**, declared via `REFERENCING OLD TABLE AS ... NEW TABLE AS ...`: read-only, statement-scoped relations holding every row the statement removed and every row it produced. You can SELECT from them, join them on the key, aggregate them, and drive a single `INSERT ... SELECT`. That is the performance lever: auditing a million-row UPDATE becomes one set-based statement rather than a million procedure calls. Support varies: SQL Server always exposed this as the `inserted` and `deleted` pseudo-tables, PostgreSQL added transition tables in version 10, Oracle offers compound triggers as its workaround, MySQL has row-level triggers only.

code

sql · 11 lines

sql

CREATE TRIGGER trg_orders_audit
  AFTER UPDATE ON orders
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION audit_orders();

-- inside audit_orders():
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

Know the OLD and NEW row images and which of them exists for INSERT, UPDATE and DELETE.

for a middle

Add that the new image is writable only in BEFORE row triggers, and that statement-level triggers need transition tables to see the changed rows.

for a senior

Use transition tables to keep bulk work set-based, and know the engine and version spread including SQL Server's inserted and deleted pseudo-tables.

for a principal

Treat the set shape as the default design for change reactions, and decide portability policy given Oracle compound triggers and MySQL's row-only model.

The two shapes of trigger data

A trigger needs to know what changed. There are exactly two useful shapes for that information, and they line up with the two firing levels.

Transition variables (row shape). A row-level trigger fires once per row, so the natural representation is a pair of row values: the row as it was, and the row as it will be or now is. Standard SQL calls these OLD and NEW; you can rename them with a REFERENCING clause, and engines expose them with slightly different syntax.

Which one exists depends on the event:

INSERT: NEW only — there was no prior row.
DELETE: OLD only — there is no resulting row.
UPDATE: both, so you can compare and detect real changes.

Mutability depends on timing: NEW is writable only in a BEFORE row trigger, because that is the only point at which the row has not yet been stored. In AFTER triggers both images are read-only snapshots.

Transition tables (set shape). A statement-level trigger fires once for a statement that may have touched a million rows, so a single row value is meaningless. Transition tables give it the set: REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows declares two read-only relations, scoped to that statement's execution, containing respectively every row version removed and every row version produced. For an UPDATE both are populated and joinable on the primary key; for an INSERT only NEW TABLE is meaningful; for a DELETE only OLD TABLE.

Why transition tables matter so much

Without them, any statement-level trigger that needs to know which rows changed is stuck — it can only see that something happened. Teams historically worked around that with ugly patterns: a row-level trigger accumulating keys into a temporary table or a session collection, then a statement-level trigger reading it. That works but reintroduces per-row cost and adds shared mutable state.

With transition tables, the entire class of "react to a change set" work becomes ordinary set-based SQL executed once:

audit history: one INSERT ... SELECT joining old and new on the key;
denormalized aggregate maintenance: one grouped update computed from the delta;
validation across the change set: one query with a NOT EXISTS, raising if any row violates.

The performance difference on bulk DML is typically an order of magnitude or more, because the optimizer plans the work as a normal query rather than executing a procedural loop.

Semantics people get wrong

An UPDATE appears in both tables. Each updated row contributes one row to OLD TABLE and one to NEW TABLE. To find what actually changed you join them on the key and compare columns — remembering that an UPDATE which writes an identical value still produces a pair, so change detection must be explicit.

They are read-only and statement-scoped. You cannot modify a transition table, and it does not exist outside that statement's trigger execution.

They reflect the statement, not the transaction. Two UPDATEs in one transaction produce two separate statement-level firings with two separate transition tables; nothing accumulates across them.

Row triggers still see rows, not sets. Declaring transition tables does not make a row-level trigger set-based. The levels remain distinct.

Interaction with cascades and rewrites. Rows changed by a foreign-key cascade or by another trigger belong to their own statement's transition tables, not the original statement's.

Engine reality

SQL Server has effectively had this since the beginning: triggers are statement-level and expose inserted and deleted pseudo-tables holding the whole change set. This is why well-written SQL Server trigger code is naturally set-based, and why porting it to a row-level engine needs care.
PostgreSQL added standard transition tables in version 10 for AFTER statement-level triggers; before that, statement triggers could not see the changed rows at all.
Oracle does not offer transition tables; the idiomatic answer is a compound trigger, a single object containing statement-level and row-level sections that share state, letting a row section collect keys into a collection and a statement-level section process them in bulk.
MySQL supports only row-level triggers, so the set shape is simply unavailable.

Because of that spread, portable trigger code either avoids statement-level set processing or is written twice.

The interview signal

Name both shapes and tie each to its firing level, state which image exists for which event and when NEW is writable, and then make the performance argument: transition tables are the mechanism that lets auditing and aggregate maintenance stay set-based on bulk DML. Mentioning that SQL Server's inserted and deleted pseudo-tables are the same idea under another name shows breadth.

## The two shapes of trigger data A trigger needs to know what changed. There are exactly two useful shapes for that information, and they line up with the two firing levels. **Transition variables** (row shape). A row-level trigger fires once per row, so the natural representation is a pair of row values: the row as it was, and the row as it will be or now is. Standard SQL calls these OLD and NEW; you can rename them with a REFERENCING clause, and engines expose them with slightly different syntax. Which one exists depends on the event: - INSERT: NEW only — there was no prior row. - DELETE: OLD only — there is no resulting row. - UPDATE: both, so you can compare and detect real changes. Mutability depends on timing: NEW is writable only in a BEFORE row trigger, because that is the only point at which the row has not yet been stored. In AFTER triggers both images are read-only snapshots. **Transition tables** (set shape). A statement-level trigger fires once for a statement that may have touched a million rows, so a single row value is meaningless. Transition tables give it the set: `REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows` declares two read-only relations, scoped to that statement's execution, containing respectively every row version removed and every row version produced. For an UPDATE both are populated and joinable on the primary key; for an INSERT only NEW TABLE is meaningful; for a DELETE only OLD TABLE. ## Why transition tables matter so much Without them, any statement-level trigger that needs to know *which* rows changed is stuck — it can only see that something happened. Teams historically worked around that with ugly patterns: a row-level trigger accumulating keys into a temporary table or a session collection, then a statement-level trigger reading it. That works but reintroduces per-row cost and adds shared mutable state. With transition tables, the entire class of "react to a change set" work becomes ordinary set-based SQL executed once: - audit history: one `INSERT ... SELECT` joining old and new on the key; - denormalized aggregate maintenance: one grouped update computed from the delta; - validation across the change set: one query with a NOT EXISTS, raising if any row violates. The performance difference on bulk DML is typically an order of magnitude or more, because the optimizer plans the work as a normal query rather than executing a procedural loop. ## Semantics people get wrong **An UPDATE appears in both tables.** Each updated row contributes one row to OLD TABLE and one to NEW TABLE. To find what actually changed you join them on the key and compare columns — remembering that an UPDATE which writes an identical value still produces a pair, so change detection must be explicit. **They are read-only and statement-scoped.** You cannot modify a transition table, and it does not exist outside that statement's trigger execution. **They reflect the statement, not the transaction.** Two UPDATEs in one transaction produce two separate statement-level firings with two separate transition tables; nothing accumulates across them. **Row triggers still see rows, not sets.** Declaring transition tables does not make a row-level trigger set-based. The levels remain distinct. **Interaction with cascades and rewrites.** Rows changed by a foreign-key cascade or by another trigger belong to *their own* statement's transition tables, not the original statement's. ## Engine reality - **SQL Server** has effectively had this since the beginning: triggers are statement-level and expose `inserted` and `deleted` pseudo-tables holding the whole change set. This is why well-written SQL Server trigger code is naturally set-based, and why porting it to a row-level engine needs care. - **PostgreSQL** added standard transition tables in version 10 for AFTER statement-level triggers; before that, statement triggers could not see the changed rows at all. - **Oracle** does not offer transition tables; the idiomatic answer is a compound trigger, a single object containing statement-level and row-level sections that share state, letting a row section collect keys into a collection and a statement-level section process them in bulk. - **MySQL** supports only row-level triggers, so the set shape is simply unavailable. Because of that spread, portable trigger code either avoids statement-level set processing or is written twice. ## The interview signal Name both shapes and tie each to its firing level, state which image exists for which event and when NEW is writable, and then make the performance argument: transition tables are the mechanism that lets auditing and aggregate maintenance stay set-based on bulk DML. Mentioning that SQL Server's inserted and deleted pseudo-tables are the same idea under another name shows breadth.

In an AFTER UPDATE statement-level trigger, how do you find only the rows whose status column actually changed?
Join the old and new transition tables on the primary key and compare the column with a null-safe predicate, because every updated row appears in both tables whether or not the value differs. A plain inequality would drop rows where one side is null, so use a null-safe form such as IS DISTINCT FROM. The result is a single set-based query over the change set.
Oracle has no transition tables. How is the equivalent achieved there?
With a compound trigger: one trigger object containing a BEFORE STATEMENT section, row-level sections, and an AFTER STATEMENT section that all share package-style state. The row sections collect the affected keys or images into a collection, and the AFTER STATEMENT section processes them in bulk. It also sidesteps the mutating-table restriction that blocks a row trigger from querying its own table.

saying these in an interview costs you the question

Referencing the old row image in an INSERT trigger or the new image in a DELETE trigger
Assuming the new row image can be modified in an AFTER trigger
Expecting transition tables to accumulate across all statements in a transaction
Thinking a row appearing in both transition tables means one of its columns changed
Assuming transition tables are available in every engine and version

Inside a trigger body, how do you reference the row's values before and after the change, and how does that differ between row-level and statement-level triggers? Explain what transition tables (REFERENCING OLD TABLE / NEW TABLE) give you.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The two shapes of trigger data

Why transition tables matter so much

Semantics people get wrong

Engine reality

The interview signal

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics3 topics

The two shapes of trigger data

Why transition tables matter so much

Semantics people get wrong

Engine reality

The interview signal

answer

go deeper

context