skip to content

In PostgreSQL, when one PL/pgSQL trigger function serves INSERT, UPDATE and DELETE, how do TG_OP, NEW and OLD behave and what should it return?

level: middleimportance: should knowfreq 47%

answer

  1. one function, three events
  2. TG_OP is a text tag
  3. NEW missing on DELETE
  4. DELETE branch returns OLD

basics

~20 s

TG_OP holds 'INSERT', 'UPDATE', 'DELETE' or 'TRUNCATE'. In row triggers NEW exists for INSERT and UPDATE, OLD for UPDATE and DELETE; both are null at statement level. A BEFORE row function returns NEW, or OLD for DELETE.

solid answer

~40 s

PL/pgSQL creates special variables in a trigger function's top-level block. `TG_OP` is the text `INSERT`, `UPDATE`, `DELETE` or `TRUNCATE`; `TG_WHEN` and `TG_LEVEL` say `BEFORE`/`AFTER`/`INSTEAD OF` and `ROW`/`STATEMENT`; `TG_TABLE_NAME`, `TG_TABLE_SCHEMA` and `TG_ARGV` identify the table and the arguments from `CREATE TRIGGER`. In row-level triggers `NEW` is set for INSERT and UPDATE and `OLD` for UPDATE and DELETE; in statement-level triggers both are null. So a shared BEFORE row function branches on `TG_OP`, returns `NEW` (possibly modified) for INSERT and UPDATE, and returns `OLD` for DELETE, because returning the null `NEW` there silently cancels the delete. If `FOR EACH` is omitted, `CREATE TRIGGER` defaults to `FOR EACH STATEMENT`, and multiple triggers on one event fire in name order.

code

sql · 19 lines
sql
CREATE FUNCTION stock_item_guard() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.qty_on_hand <> 0 THEN
      RAISE EXCEPTION 'cannot delete %: % still on hand',
        OLD.sku, OLD.qty_on_hand;
    END IF;
    RETURN OLD;
  END IF;
  NEW.sku := upper(NEW.sku);
  RETURN NEW;
END;
$$;

CREATE TRIGGER stock_item_guard
  BEFORE INSERT OR UPDATE OR DELETE ON stock_item
  FOR EACH ROW
  EXECUTE FUNCTION stock_item_guard();

go deeper

for a junior

Know that NEW is the new row and OLD the old row in row-level triggers, and that TG_OP tells you which operation fired.

for a middle

Explain the null cases (NEW on DELETE, both at statement level), the per-branch return rule, the FOR EACH STATEMENT default and name ordering of triggers.

for a senior

Spot the shared-function bug where RETURN NEW cancels deletes, and design generic functions with TG_ARGV, TG_LEVEL checks and explicit returns that survive reuse across tables.

for a principal

Decide how far a shared trigger library should go before its indirection costs more in debugging than duplicated, table-specific triggers would.

## The variables a PL/pgSQL trigger function gets A PostgreSQL trigger function is declared `RETURNS trigger` with no arguments. When PL/pgSQL calls it as a trigger, it creates a set of special variables in the function's top-level block. They are how one function can serve several events and several tables. | Variable | Type | Holds | |---|---|---| | `TG_OP` | `text` | `INSERT`, `UPDATE`, `DELETE` or `TRUNCATE` | | `TG_WHEN` | `text` | `BEFORE`, `AFTER` or `INSTEAD OF` | | `TG_LEVEL` | `text` | `ROW` or `STATEMENT` | | `TG_NAME` | `name` | the trigger that fired | | `TG_TABLE_NAME`, `TG_TABLE_SCHEMA` | `name` | the table and its schema (`TG_RELNAME` is the deprecated older name) | | `TG_RELID` | `oid` | the table's OID | | `TG_NARGS`, `TG_ARGV` | `integer`, `text[]` | the arguments written in `CREATE TRIGGER`, indexed from 0 | | `NEW`, `OLD` | `record` | the new and old row, where they exist | `TG_ARGV` deserves a note: the arguments in `CREATE TRIGGER ... EXECUTE FUNCTION f('qty_on_hand')` are string literals, and an index outside the range simply yields null, so a generic function should check `TG_NARGS` first. ## NEW and OLD by event and level | Firing | `NEW` | `OLD` | |---|---|---| | row-level INSERT | the row being inserted | null | | row-level UPDATE | the proposed new version | the current version | | row-level DELETE | null | the row being deleted | | any statement-level trigger | null | null | Statement-level triggers never see individual rows through these variables; an AFTER statement trigger that needs the affected rows declares transition tables instead. A `TRUNCATE` trigger can only be statement-level, so it never has `NEW` or `OLD`. ## What each branch must return For a BEFORE row trigger, the return value is the row the executor will use, so a shared function has to return the right thing per operation: 1. **INSERT and UPDATE**: return `NEW`, after any edits such as normalising the SKU. Returning a different row changes what is stored. 2. **DELETE**: return `OLD`, or any non-null value. The value has no further effect, but it must be non-null for the delete to happen. 3. **To veto a row without an error**: return `NULL`. The operation is skipped for that row and later triggers do not run for it. 4. **AFTER row and all statement-level triggers**: the return value is ignored, so `RETURN NULL` is the convention; such a trigger can still abort the whole statement by raising an error. The bug that a single shared function invites is a generic `RETURN NEW` at the end. For `DELETE`, `NEW` is null, so the function returns null and every delete is silently skipped: the command reports `DELETE 0` and nothing is logged. ## Firing order and conditional firing - If neither `FOR EACH ROW` nor `FOR EACH STATEMENT` is written, `CREATE TRIGGER` creates a **statement-level** trigger. A statement trigger fires even when the statement affects zero rows. - Several triggers for the same event on the same table fire in **alphabetical order by name**; the SQL standard specifies creation order, and PostgreSQL deliberately differs. Each BEFORE trigger receives the row the previous one returned, and a `NULL` from any of them ends the chain for that row. - A `WHEN (...)` clause filters before the function is called. Row-level conditions may read `OLD.col` and `NEW.col`, but an INSERT trigger cannot reference `OLD`, a DELETE trigger cannot reference `NEW`, and the expression may not contain subqueries. - `CREATE OR REPLACE TRIGGER` swaps a trigger definition in place, which avoids a drop-and-create window in a migration. ## Habits that keep shared trigger functions safe - Branch explicitly on `TG_OP` and end every branch with its own `RETURN`. - Check `TG_LEVEL` or `TG_WHEN` and raise an error if the function is attached in a way it was not written for. - Keep table-specific details in `TG_ARGV` rather than cloning the function per table. - Prefer `TG_TABLE_NAME` over the deprecated `TG_RELNAME`.

  • What happens if the DELETE branch of that function ends with RETURN NEW?
    In a row-level DELETE trigger `NEW` is null, so the function returns null. For a BEFORE row trigger that means skip this row: every delete on the table is silently cancelled, the command tag reports zero rows, and no error is raised. Returning `OLD` is the idiom.
  • Two BEFORE INSERT triggers, b_audit and a_defaults, exist on stock_item. Which runs first and what does the second one see?
    PostgreSQL fires triggers for the same event in alphabetical order by name, not creation order as the SQL standard specifies. So `a_defaults` runs first, and `b_audit` receives whatever row `a_defaults` returned. If `a_defaults` returned null, `b_audit` would not run for that row at all.
  • How does one generic trigger function learn which column to stamp on different tables?
    Pass it in `CREATE TRIGGER ... EXECUTE FUNCTION f('updated_at')`. The arguments are string literals that reach PL/pgSQL as `TG_ARGV`, a `text[]` indexed from 0, with `TG_NARGS` giving the count; an out-of-range index yields null. Setting a column chosen at run time then needs dynamic code, which is the PL/pgSQL leaf's subject.

  • In a DELETE row trigger NEW holds the deleted row, so RETURN NEW is fine.
  • In a statement-level trigger NEW holds the last row the statement changed.
  • CREATE TRIGGER without FOR EACH defaults to a row-level trigger.
  • Triggers on the same event fire in the order they were created.
  • TG_ARGV arguments arrive with their original types, such as integer or boolean.