For keeping a derived value in a table up to date, when would you use a generated column and when would you fall back to a trigger? What does each guarantee?
answer
- declarative guarantee vs procedural behaviour
- same row + deterministic => generated column
- other rows / clock / freeze-at-insert => trigger
- triggers can be disabled and bypassed; generated cannot
- per-row trigger invocation cost on bulk writes
basics
~20 sUse a generated column whenever the value is a deterministic function of the same row - the engine guarantees it can never be wrong or bypassed, and there is no procedural code to maintain. Use a trigger only when the derivation needs other rows, other tables, non-deterministic input, or must freeze at insert time.
solid answer
~60 sA generated column is a declarative constraint on a value: the database recomputes it on every write, no code path can set it to something else, and existing rows are consistent by construction. A trigger is procedural code you own - it can be disabled, it can be skipped by bulk-load paths that suppress triggers, it fires in an order relative to other triggers, and it must be tested and versioned like any program. So the decision is mostly capability, not taste. Reach for a trigger only when the generated column cannot express the need: - the value depends on **other rows or other tables** (a running balance, a denormalized parent counter); - it must capture **non-deterministic input** (an update timestamp, the current user); - it should be **set once and then frozen**, or overridable by the application; - you cannot take the table rewrite that adding a stored column implies right now. Otherwise generated columns win on correctness, on being self-documenting, and on cost - no per-row procedure invocation, no ordering surprises.
code
sql · 16 lines-- declarative: cannot drift, cannot be bypassed
ALTER TABLE order_lines
ADD COLUMN line_total_c integer
GENERATED ALWAYS AS (unit_price_c * qty - discount_c) STORED;
-- procedural: needed because the clock is not deterministic
CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER order_lines_touch
BEFORE UPDATE ON order_lines
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();go deeper
State the rule: if the value comes from the same row and the formula is fixed, use a generated column; use a trigger when you need the clock or other tables.
Add the guarantee difference and the cost difference - per-row procedure calls, ordering, and the fact that a trigger fixes only future rows.
Discuss drift detection, backfill and migration path in both directions, disabled-trigger incidents, and the option of not storing the value at all.
Argue about where derived state should live overall - column, view, materialized view, or event-driven projection - and what each implies for operability, concurrency, and schema evolution.
## Two different kinds of promise A generated column is declarative: you state the relationship between columns and the engine is responsible for it. It cannot drift, because there is no path that writes the column at all - direct writes are rejected, and every insert and update recomputes it. If you change the expression, the engine rewrites the existing data so old rows obey the new rule. That is the same category of guarantee as a CHECK constraint: a property of the data, not a behaviour of some code. A trigger is procedural: a function the engine calls on your behalf at a defined point in the write path. It gives you full generality - reading other tables, writing other rows, branching on old versus new values - and in exchange it gives up the guarantee. Triggers can be disabled (temporarily, for a migration, and then forgotten), can be skipped by paths that intentionally suppress them, and can leave rows written while they were off silently wrong. Nothing in the schema tells a future reader that a column is trigger-maintained; they have to find the trigger. ## Cost and behaviour differences - **Per-row invocation.** A row-level trigger calls a procedure for every affected row. On a bulk update of a million rows that is a million procedure invocations, often the dominant cost. A generated column evaluates an inline expression. - **Ordering and interaction.** Multiple triggers on one table fire in a defined but easy-to-forget order, and a BEFORE trigger that mutates the new row interacts with other triggers and with constraints. Generated columns have no ordering question. - **Recursion and cascades.** Triggers can fire other triggers, including on the same table, which is a familiar source of runaway or surprising behaviour. Generated columns cannot cascade. - **Visibility.** A generated column shows its formula in the table definition. A trigger hides the rule in a separate object with its own name. - **Backfill.** Adding a trigger fixes the future only; you still need a backfill for existing rows, and it can drift again if the trigger is ever bypassed. Adding a generated column fixes past and future in one operation - at the price of that rewrite. ## When the trigger is genuinely the right tool 1. **Cross-row or cross-table derivation.** A denormalized order count on customers, a running balance, a search vector assembled from a child table - a generated column cannot see other rows, so this is trigger (or application, or materialized view) territory. 2. **Non-deterministic capture.** Setting an update timestamp to the current clock on every update is the canonical example. It must be captured, not derived, so it is a trigger or an explicit write in the application. (An insert timestamp needs only a DEFAULT.) 3. **Set-once or overridable semantics.** A snapshot value that should freeze at insert - a price at time of sale, a customer tier at time of order - must not be recomputed later. A generated column would helpfully and wrongly recompute it on every update. 4. **Migration constraints.** If you cannot take the rewrite that adding a STORED column implies on a hot multi-terabyte table right now, a trigger plus a batched backfill is the online-friendly path, possibly as a stepping stone. 5. **Expressions the engine will not accept.** A derivation requiring a function you cannot honestly mark immutable belongs in procedural code where its volatility is not a lie. ## A third option people forget Before choosing either, ask whether the value should be stored at all. If it is a pure function of the row and read rarely, a VIRTUAL generated column or just a view computing it costs nothing to maintain. If it aggregates other rows and staleness is acceptable, a materialized view with a refresh policy is often cleaner than a trigger that must be correct under concurrency. Triggers maintaining cross-row aggregates are notoriously hard to get right when two transactions touch the same parent, and they serialize writes on the aggregate row. ## How to answer in an interview State the rule crisply - deterministic and same-row means generated column, anything else means trigger or application logic - then justify it with the guarantee difference (cannot be bypassed versus can be disabled), then name the two or three cases where you would still write the trigger, and mention that you would prefer not to store the value at all if the read pattern does not justify it.
- An existing table maintains a derived total with a trigger. You want to convert it to a generated column. What is your migration plan?First verify the trigger's formula is genuinely same-row and deterministic, and reconcile existing rows against it to find drift. Then, in a maintenance window or with an online schema-change tool, drop the trigger and add the STORED generated column - the add recomputes every row, which repairs any drift as a side effect. Expect a full table rewrite under a heavy lock, so size it against the table and plan a batched shadow-column approach if the lock is unaffordable.
- Why is a generated column a poor fit for a price snapshot such as price_at_purchase?Because a generated column is re-derived on every write, so if the referenced price ever changes the historical row changes with it, destroying the snapshot. Snapshot semantics require capturing the value once and then treating it as ordinary immutable data - written by the application or a BEFORE INSERT trigger. The give-away is the phrase 'at the time of': anything time-of-event is captured, not derived.
A generated column is a law of physics in the schema; a trigger is a security guard. The law cannot be switched off, the guard can call in sick.
saying these in an interview costs you the question
- Claiming a trigger gives the same guarantee as a generated column, ignoring that triggers can be disabled or bypassed
- Using a generated column for a value that must freeze at insert time
- Reaching for a trigger to maintain a same-row derived value the column can express declaratively
- Forgetting that adding a trigger does not fix existing rows, so a backfill is still required
- Ignoring per-row trigger cost and cascade/order effects on bulk writes