beginnerintermediateadvanced

PostgreSQL · Triggers

Triggers

BEFORE/AFTER/INSTEAD OF timing, row vs statement granularity, where triggers genuinely earn their keep, and the ordering/cascading/debuggability costs that come with them

BEFORE can vetoreturn NULL skips the row entirely
Row vs statementonce per row, or once per statement
Fires alphabeticallyby trigger name, not creation order
Invisible from app codeno stack trace entry, no call-site signal

1.BEFORE, AFTER and INSTEAD OF Triggers

BEFORE triggers fire before the write is attempted and can modify the row or cancel the operation entirely (by returning NULL). AFTER triggers fire once the write has actually happened and cannot change it — they see the final state. INSTEAD OF triggers replace the operation entirely and only exist on views, which have no storage of their own to write to.

Key features

BEFORE row-level triggers can return a modified NEW row (INSERT/UPDATE) or return NULL to silently skip the operation for that row
AFTER triggers cannot modify the row or cancel the operation — the write has already happened by the time they run
INSTEAD OF triggers are only valid on views, are always row-level (FOR EACH ROW), and their return value is ignored
A BEFORE trigger must return the same row unchanged if it does not intend to modify or skip it — returning nothing changes behavior
For generated columns, BEFORE triggers see the OLD generated value but not yet the NEW one; only AFTER triggers see the final NEW value

BEFORE vs AFTER vs INSTEAD OF

BEFORE vs AFTER vs INSTEAD OF
Timing	Can modify/cancel the write?	Valid on
BEFORE	yes — modify NEW, or return NULL to skip	tables, foreign tables (row); tables, views, foreign tables (statement)
AFTER	no — the write already happened	tables, views, foreign tables
INSTEAD OF	replaces the operation entirely	views only, row-level only

Where each trigger timing sits relative to the write

may vetoor reshapealready committedto this transaction

BEFORE

can modify NEW or return NULL to skip

the actual write

row is inserted/updated/deleted

AFTER

sees the final state, cannot change it

Command

sql

-- BEFORE: normalize a value before it is stored
CREATE TRIGGER normalize_email BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION lower_email();

-- AFTER: react to a write that has already happened
CREATE TRIGGER log_order_insert AFTER INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION write_audit_log();

-- INSTEAD OF: a view has no rows to write to directly
CREATE TRIGGER insert_via_view INSTEAD OF INSERT ON active_users_view
    FOR EACH ROW EXECUTE FUNCTION redirect_insert_to_users();

BEFORE can modify NEW or return NULL to veto the write; AFTER only observes a write that already happened and cannot undo it; INSTEAD OF replaces the operation entirely and exists only on views, which have nothing of their own to write to.

2.Row-Level vs Statement-Level Triggers

FOR EACH ROW fires the trigger once per affected row — a DELETE removing 10 rows fires it 10 times, with OLD/NEW bound to each one. FOR EACH STATEMENT fires exactly once per SQL statement regardless of how many rows it touches, even zero, and has no access to OLD/NEW for individual rows.

Key features

FOR EACH ROW: once per affected row, with OLD and/or NEW bound to that row's values
FOR EACH STATEMENT: exactly once per statement, even if zero rows were affected — no per-row OLD/NEW
FOR EACH STATEMENT is the default if neither is specified
A statement-level trigger fires once even for an UPDATE that touches 100,000 rows — a row-level trigger on the same UPDATE fires 100,000 times
PostgreSQL also supports transition tables (REFERENCING OLD TABLE/NEW TABLE) for a statement-level trigger to see the full set of changed rows at once

FOR EACH ROW vs FOR EACH STATEMENT

FOR EACH ROW vs FOR EACH STATEMENT
Property	FOR EACH ROW	FOR EACH STATEMENT
Fires	once per affected row	once per statement
Access to OLD/NEW	yes, for that row	no (unless using transition tables)
Cost on a 10,000-row UPDATE	10,000 invocations	1 invocation
Good fit	per-row validation or derived values	cache refresh, bulk-operation notification

A DELETE removing 4 rows — how many times does each trigger fire?

FOR EACH ROW

Fires once per affected row
OLD/NEW bound to that specific row
4-row DELETE → 4 invocations

FOR EACH STATEMENT

Fires exactly once per statement
No per-row OLD/NEW (unless using transition tables)
4-row DELETE → 1 invocation

Command

sql

-- row-level: needs each row's own OLD/NEW
CREATE TRIGGER touch_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- statement-level: only cares that a bulk delete happened at all
CREATE TRIGGER refresh_stats AFTER DELETE ON orders
    FOR EACH STATEMENT EXECUTE FUNCTION refresh_order_stats();

FOR EACH ROW fires once per affected row with that row's OLD/NEW bound; FOR EACH STATEMENT fires exactly once per statement regardless of row count. Use row-level when the logic genuinely needs a specific row's values; use statement-level when it only needs to know an operation of a certain kind happened.

3.Appropriate Uses for Triggers

Triggers are the right tool when the logic must hold true regardless of which application or code path performs the write — maintaining an audit trail, keeping a derived column (like updated_at, or a denormalized total) consistent, or enforcing an invariant too complex for a plain CHECK constraint.

Key features

Audit trails: an AFTER trigger logging who changed what and when, independent of which application code path made the change
Derived/denormalized columns: keeping updated_at current, or maintaining a running total that must stay in sync with detail rows
Invariants too complex for a single CHECK constraint (which cannot reference other rows or other tables) but still fundamentally about data correctness
Tightly coupled derived behavior: cascading a status change to a related row in the same transaction, when that coupling is a data-level fact, not a business workflow
A CHECK constraint is preferred over a trigger whenever the rule is expressible as one — it is simpler, self-documenting in \d output, and cannot be silently disabled the way a trigger can

Good trigger use cases

Good trigger use cases
Use case	Why a trigger fits
Audit trail	must capture every write, regardless of caller
updated_at maintenance	a data-level fact true for every row, every writer
Cross-row invariant	CHECK constraints cannot reference other rows/tables
Denormalized total kept in sync	must stay correct even if maintained outside the app's main write path

Command

sql

CREATE FUNCTION log_order_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO order_audit (order_id, changed_at, old_status, new_status)
    VALUES (NEW.id, now(), OLD.status, NEW.status);
    RETURN NEW;
END;
$$;
CREATE TRIGGER audit_order_status AFTER UPDATE OF status ON orders
    FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION log_order_change();

Triggers earn their keep for cross-row/cross-table invariants, audit trails, and derived values that must stay correct regardless of which caller writes the data — but whenever a plain CHECK constraint can express the same rule, prefer it: simpler, self-documenting, and cannot be silently disabled.

4.Trigger Ordering and Hidden Side Effects

When multiple triggers of the same timing and level exist on the same table and event, PostgreSQL fires them in alphabetical order by trigger name — not creation order. If a trigger function runs SQL that fires other triggers (including, potentially, itself), that is a cascading trigger, and nothing in PostgreSQL limits how many levels deep that cascade can go.

Key features

Multiple triggers of the same timing/level/event on one table fire in alphabetical order by trigger name — not the order they were created
A trigger function that runs an INSERT/UPDATE/DELETE can fire other triggers, including on other tables — this is called cascading
A trigger can recursively re-fire itself (e.g. an INSERT trigger that inserts another row into the same table) — PostgreSQL places no automatic limit on cascade depth
The programmer is fully responsible for preventing infinite recursion; there is no built-in safeguard
A prefix naming convention (e.g. 01_, 02_) is a common, deliberate way to make trigger firing order visible and controllable

What can silently go wrong with triggers

What can silently go wrong with triggers
Hazard	Why it is hidden
Fire order depends on trigger name	not visible from CREATE TRIGGER statement order in a migration file
Cascading across tables	a single INSERT can fan out with no indication in the original statement
Recursive self-firing	no automatic depth limit — an unguarded case can loop indefinitely

A cascade: one INSERT quietly fans out

AFTER INSERTthe UPDATE itruns fires thisthe INSERT itruns fires this

INSERT INTO orders

the only statement the caller sees

Trigger: deduct_inventory

fires an UPDATE on inventory

Trigger: check_low_stock

fires an INSERT on stock_alerts

Trigger: notify

pg_notify(), no further writes

Command

sql

-- naming convention that makes fire order visible and deliberate
CREATE TRIGGER "01_validate" BEFORE INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION validate_order();
CREATE TRIGGER "02_normalize" BEFORE INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION normalize_order();
-- fires 01_validate, then 02_normalize -- alphabetical, made intentional by the prefix

Same-timing triggers on one table fire in alphabetical order by name, not creation order — use a numeric prefix if order matters. A trigger's own writes can fire other triggers (cascading), including itself (recursion), with no automatic depth limit — any trigger that might re-fire itself needs its own explicit guard.

5.Why Trigger-Only Workflows Are Harder to Reason About

A workflow built entirely out of triggers is invisible from the application code that ultimately causes it — a developer reading "INSERT INTO orders" has no way to know, without reading the schema's trigger definitions directly, that the insert also updates inventory, writes an audit row and sends a notification. That invisibility is exactly what makes trigger-only workflows hard to debug, test and change safely.

Key features

A trigger-driven workflow does not appear in the calling code's stack trace the way a function call does — debugging requires knowing to check pg_trigger for the table involved
Unit-testing a trigger workflow generally requires a real database connection, unlike pure application logic that can often be tested in isolation
Code review of an INSERT statement gives no visual signal that triggers will fire — a reviewer has to already know the schema's trigger definitions
Trigger definitions live in schema migrations, separate from the application's normal code review and versioning workflow, which can drift out of sync with the mental model developers have of "what happens on write"
None of this means triggers are wrong to use — it means the harder-to-trace cost should be weighed against the benefit deliberately, not accepted by default

Debuggability: application code vs trigger-only workflow

Debuggability: application code vs trigger-only workflow
Property	Application code	Trigger-only workflow
Visible in a stack trace	yes	no
Testable without a real database	often	rarely
Visible to a reviewer reading the call site	yes	no — must already know the schema
Findable by searching application code	yes	no — lives in migrations/pg_trigger

Command

sql

-- what a reviewer sees in the application's pull request:
-- INSERT INTO orders (customer_id, total) VALUES ($1, $2);

-- what actually happens, invisible from that line alone:
-- \d+ orders  -- lists every trigger attached to this table
--   Triggers:
--     deduct_inventory AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION deduct_inventory_fn()
--     audit_order_insert AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION write_audit_log()

A trigger-only workflow is invisible from the application code that triggers it — no stack trace entry, no easy unit test, no signal at the call site for a reviewer. That cost does not make triggers wrong, but it means the decision to put critical business logic only in triggers should be deliberate, with \d+ or equivalent documentation as the mitigation, not an afterthought.

Spotted a mistake on this page? Tell us.

Read Sheet