The Trigger Model

A trigger is a function PostgreSQL calls automatically when a specific DML event happens on a table, and the entire model is built around answering one question: exactly when, and how many times, does that call happen relative to the write it's reacting to.

Getting that timing model right is what separates a trigger that quietly validates or audits data from one that silently corrupts it, deadlocks under load, or fires far more often than the author intended.

This page builds the mental model behind timing, row-versus-statement firing, and the executor's interaction with MVCC so that Triggers Basics, Transition Tables, Audit Triggers, and Rules (Legacy) read as one coherent system rather than a pile of syntax variants.

Summary

• Core Idea: A trigger binds a function to a DML event with a specific timing (BEFORE/AFTER/INSTEAD OF) and granularity (ROW/STATEMENT), and PostgreSQL calls that function automatically whenever the event matches.
• Why It Matters: The timing and granularity you choose determine whether the trigger can still change the data, how many times it runs, and what row state it's actually allowed to see.
• Key Concepts: timing (BEFORE/AFTER/INSTEAD OF), granularity (ROW/STATEMENT), NEW/OLD, transition table, trigger ordering.
• When to Use: Enforcing invariants a CHECK constraint can't express, maintaining derived or audit data automatically, or intercepting writes to a view with INSTEAD OF.
• Limitations / Trade-offs: Every trigger adds invisible cost to every matching write, and logic hidden in a trigger is easy to forget about when someone is debugging unexpected data changes months later.
• Related Topics: MVCC row visibility, constraint enforcement, logical replication, the legacy rule system.

Foundations

Every trigger is defined by two independent choices: its timing (BEFORE, AFTER, or INSTEAD OF) and its granularity (FOR EACH ROW or FOR EACH STATEMENT).

BEFORE ROW triggers run once per affected row before the write happens, and they are the only kind that can modify the row through NEW or cancel the operation entirely by returning NULL.

AFTER ROW triggers run once per affected row after the write has happened, and they can look at the final row but cannot change what got written, they can only react to it.

INSTEAD OF triggers exist only on views, and they replace the write entirely, which is how a normally read-only view can accept INSERT, UPDATE, or DELETE statements.

STATEMENT-level triggers fire exactly once per SQL statement regardless of how many rows it touched, trading per-row detail for a single, cheap invocation.

A useful analogy is a security checkpoint on an assembly line: a BEFORE ROW trigger is a checkpoint that can still reject or relabel an item before it leaves the station, while an AFTER ROW trigger is a camera further down the line that only records what already shipped.

Mechanics & Interactions

Every trigger function returns the special pseudo-type trigger, and for row-level triggers the return value has meaning: a BEFORE trigger returning NULL cancels the operation for that row, while returning NEW (possibly modified) lets it proceed.

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

When multiple triggers share the same table and timing, PostgreSQL fires them in alphabetical order by trigger name, not creation order, which is why teams that need a predictable sequence prefix trigger names deliberately.

AFTER triggers run within the same transaction as the write that fired them, so any row they read reflects that transaction's own uncommitted changes under normal MVCC visibility rules, but nothing about the trigger being AFTER means the outer transaction has committed yet.

Transition tables, declared with REFERENCING NEW TABLE AS ... OLD TABLE AS ..., expose the full set of rows a statement-level trigger affected as if it were a real table, giving bulk access to changed rows without paying the per-row cost of a row-level trigger.

Row-level:        row 1 -> fire   row 2 -> fire   row 3 -> fire   (N calls)
Statement-level:   [row1, row2, row3] -> fire once (1 call, sees all rows via transition table)

A WHEN clause lets a row-level trigger skip firing entirely when a condition on OLD/NEW isn't met, which is evaluated before the trigger function is even invoked and is meaningfully cheaper than checking the same condition as the first line inside the function body.

Advanced Considerations & Applications

The older rule system (CREATE RULE) works by rewriting the incoming query itself into a different query before execution, which is a fundamentally different mechanism from a trigger's per-row or per-statement function call, and it is why rules can silently change row counts returned by commands in ways triggers never do.

PostgreSQL's own documentation steers most new code toward triggers and INSTEAD OF triggers on views instead of rules, since rules interact confusingly with multi-statement commands and are much harder to reason about; the one place rules still matter in practice is INSTEAD OF-style view rewriting that predates the cleaner view-trigger mechanism.

Triggers also interact with logical replication in a way that surprises people: by default, a trigger on a subscriber-side table does not fire for rows applied by replication, and you have to explicitly mark it ENABLE REPLICA TRIGGER or ENABLE ALWAYS TRIGGER if you need it to run there too.

A well-known operational failure mode is the cascading trigger chain, where a trigger on table A updates table B, which fires a trigger that updates table A again, and without a deliberate stop condition that chain can loop or simply make an innocuous-looking write far slower than it appears.

Common Misconceptions

• "Triggers run in their own separate transaction." A trigger executes inside the exact same transaction as the statement that fired it, so if that transaction rolls back, everything the trigger did rolls back too.
• "Returning NULL from any trigger cancels the write." Only BEFORE ROW triggers can cancel an operation this way, an AFTER trigger's return value is ignored entirely.
• "Trigger firing order follows creation order." PostgreSQL fires same-timing triggers in alphabetical order by trigger name, which is why deliberate naming prefixes matter for ordering-sensitive logic.
• "Rules and triggers are basically the same feature with different syntax." A rule rewrites the query itself before execution, while a trigger calls a function per row or per statement after the query is already planned, and the two produce very different behavior on multi-row commands.
• "A statement-level trigger can't see which rows changed." A statement-level trigger with a transition table can see every affected row at once, it just can't reference NEW/OLD for a single row the way a row-level trigger can.

FAQs

Related

• Triggers Basics - BEFORE/AFTER, row vs statement, hands-on
• Transition Tables - statement-level bulk OLD/NEW access
• Audit Triggers - change capture patterns without killing write throughput
• Rules (Legacy) - the query-rewrite mechanism triggers mostly replaced
• When Not to Use Triggers - the trade-off against application-level logic
• PL/pgSQL Explained - the language every trigger function is written in

Stack versions: This page was written for PostgreSQL 18.4 (stable line 18, maintenance line 17), where the trigger timing model, transition tables, and replica-trigger firing behavior described here are current.