skip to content

In PostgreSQL, how do you build a BEFORE UPDATE row trigger that stamps an updated_at column, and what must its trigger function return?

level: juniorimportance: must knowfreq 68%

answer

  1. function first, trigger second
  2. RETURNS trigger, no arguments
  3. assign to NEW, hand it back
  4. NULL means skip this row
  5. now() is transaction start

basics

~20 s

Write a function declared RETURNS trigger that sets NEW.updated_at and returns NEW, then attach it with CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW EXECUTE FUNCTION. Returning NULL instead silently skips that row's update.

solid answer

~50 s

A PostgreSQL trigger is two objects: a function declared `RETURNS trigger` with no arguments, and a `CREATE TRIGGER` that binds it to the table, here `BEFORE UPDATE ON stock_item FOR EACH ROW EXECUTE FUNCTION stock_item_touch()`. In PL/pgSQL the function assigns `NEW.updated_at` and must `RETURN NEW`, because in a BEFORE row trigger the returned row is what gets written. Returning `NULL` skips the operation for that row without an error and stops later triggers for it, which is a classic silent-data-loss bug; an AFTER row trigger's return value is ignored. Choose the clock on purpose: `now()` is the transaction's start time, so every row one transaction touches gets the same stamp, while `clock_timestamp()` is wall time. A `WHEN (OLD.* IS DISTINCT FROM NEW.*)` clause stops no-op updates being stamped, as long as the table has no generated columns.

code

sql · 19 lines
sql
CREATE TABLE stock_item (
  sku         text PRIMARY KEY,
  qty_on_hand integer NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION stock_item_touch() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER stock_item_touch
  BEFORE UPDATE ON stock_item
  FOR EACH ROW
  WHEN (OLD.* IS DISTINCT FROM NEW.*)
  EXECUTE FUNCTION stock_item_touch();

go deeper

for a junior

Recall the two-step shape: a function that RETURNS trigger with no arguments, then CREATE TRIGGER ... BEFORE UPDATE ... FOR EACH ROW EXECUTE FUNCTION. Say that the function sets NEW.updated_at and returns NEW.

for a middle

Explain why the return value matters in a BEFORE row trigger: the returned row is written, NULL skips the row silently, and AFTER row return values are ignored. Mention name order when several triggers modify the row.

for a senior

Show judgement about the clock (transaction time versus clock_timestamp()), about no-op updates (a WHEN guard, the generated-column caveat, suppress_redundant_updates_trigger), and about how silent NULL returns surface as missing writes.

for a principal

Frame stamping as a contract: who owns updated_at, whether clients may set it, which clock the business means, and whether a trigger or the application layer should enforce it across every write path.

## Two objects: a trigger function and a trigger PostgreSQL never runs a bare SQL block as a trigger body. A **trigger** is a catalog object that binds a **trigger function** to a table, an event and a timing, so you always create two things, in this order: 1. A function declared `RETURNS trigger` that takes **no arguments**. Row data does not arrive as parameters; in PL/pgSQL it appears in the special variables `NEW` and `OLD`, and any arguments written in `CREATE TRIGGER` arrive as the text array `TG_ARGV`. 2. A `CREATE TRIGGER` statement naming the timing (`BEFORE`), the event (`UPDATE`), the table, the granularity (`FOR EACH ROW`) and the function (`EXECUTE FUNCTION stock_item_touch()`). The spelling `EXECUTE PROCEDURE` is still accepted, but it is historical and deprecated: the object it names must be a function, never a procedure. One function can serve many triggers on many tables, which is how a single touch function stamps `updated_at` across a whole warehouse schema. ## What a BEFORE row trigger returns In a `BEFORE ... FOR EACH ROW` trigger the return value is not a formality: it is the row the executor will write. | Trigger returns | Effect on the current row | |---|---| | `NEW`, modified | The modified row is inserted, or becomes the new version of the updated row | | `NEW`, unchanged | The operation proceeds exactly as the statement asked | | `NULL` | The operation is skipped for this row, silently, and later triggers do not fire for it | | anything, from an AFTER row trigger | Ignored, because the row is already written | The `NULL` case is the one interviewers probe. It is a deliberate feature, since a trigger can veto a row without raising an error, but it is also a classic bug: a function that reaches `RETURN NULL` by accident, or returns `NEW` in a `DELETE` trigger where `NEW` is null, quietly turns writes into no-ops. The command tag reports fewer rows than the application expected, and nothing is logged. For a `BEFORE DELETE` trigger the usual idiom is `RETURN OLD`. When several triggers exist for the same event, PostgreSQL fires them in **name order**, and each BEFORE trigger receives the row the previous one returned. That is why an AFTER trigger is the place to read the final row and a BEFORE trigger is the place to change it. A BEFORE trigger is also cheaper, because nothing has to be queued until the end of the statement. ## Choosing the clock The obvious body, `NEW.updated_at := now();`, has a property worth stating out loud: - `now()`, which is the same as `transaction_timestamp()` and `CURRENT_TIMESTAMP`, returns the **start time of the current transaction**, so every row a transaction touches gets the same stamp, even if the update runs an hour later. - `statement_timestamp()` returns the start time of the current statement. - `clock_timestamp()` returns the actual current time and changes even within one statement. Transaction time is usually right for consistency, because one business operation gets one timestamp. Wall time is right when rows inside one long batch must be ordered by when they were really written. Either way, store it in a `timestamptz` column, which records an instant rather than a local wall-clock reading. ## Keeping the stamp honest An UPDATE that sets a column to its current value still writes a new row version in PostgreSQL, and an unconditional stamper then changes `updated_at` as well, so a no-op looks like a real change. The tools available: - `WHEN (OLD.* IS DISTINCT FROM NEW.*)` on the trigger means the function is not even called for an unchanged row. In a BEFORE trigger the condition sees `NEW` as possibly modified by earlier triggers, and the whole-row `NEW` reference is rejected if the table has generated columns; then compare the relevant columns one by one. - `UPDATE OF qty_on_hand` limits firing to statements that list that column in `SET`. It does not compare values, so `SET qty_on_hand = qty_on_hand` still fires it. - The built-in `suppress_redundant_updates_trigger()` skips updates that change nothing. The manual names its example `z_min_update` so that, in name order, it fires after every trigger that might alter the row. A generated column is no substitute: a PostgreSQL generation expression may only use immutable functions, so `now()` is refused, and a BEFORE trigger's changes to a stored generated column are ignored and overwritten. ## Mistakes that show in an interview - Declaring the function with a row parameter instead of `RETURNS trigger` and no arguments. - Returning `NULL` from a BEFORE row trigger to mean nothing changed. - Using an AFTER trigger to change `NEW`; the row is already stored and the return value is ignored. - Assuming `now()` differs from row to row inside one transaction. - Believing `UPDATE OF` compares the old and new values.

  • Why not make updated_at a generated column instead of using a trigger?
    PostgreSQL generation expressions may use only immutable functions, and `now()` and `clock_timestamp()` are not immutable, so the table definition is rejected. A column `DEFAULT now()` covers inserts only. And a BEFORE trigger cannot set a stored generated column either: such changes are ignored and overwritten once the BEFORE triggers have run.
  • The stock_item table also uses suppress_redundant_updates_trigger(); how do trigger names and the stamper interact?
    PostgreSQL fires same-event triggers in name order, and each BEFORE trigger receives the row the previous one returned. The manual names the suppressor `z_min_update` so it runs last. An unconditional stamper changes `updated_at` on every row, so the suppressor never finds a no-op. Guard the stamper with a `WHEN` clause so an unchanged row reaches the suppressor unchanged and is skipped.
  • When would you choose clock_timestamp() over now() in this trigger?
    When rows written inside one long transaction must be distinguishable or ordered by the moment they were really changed, for example a batch that adjusts stock for hours. `now()` gives every row the transaction's start time; `clock_timestamp()` gives the real time at each call, at the cost of rows in one logical operation carrying different stamps.

  • A BEFORE row trigger can return NULL when it has nothing to change.
  • The trigger function receives the changed row as an ordinary function argument.
  • now() inside the trigger gives each row the moment that row was written.
  • UPDATE OF qty_on_hand fires only when the qty_on_hand value actually changes.
  • EXECUTE PROCEDURE means the trigger calls a stored procedure rather than a function.