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?
answer
- one function, three events
- TG_OP is a text tag
- NEW missing on DELETE
- DELETE branch returns OLD
basics
~20 sTG_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 sPL/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 linesCREATE 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
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.
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.
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.
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.