7.2.9 — Triggers, and How They Differ From Everything Else

Someone changed a customer's email address in production and nobody can say who, when, or what it was before. There is no record, because the UPDATE came from a script and the application's audit code never ran.

A trigger is a piece of code the database runs automatically whenever a table changes. It does not matter who did the writing — your application, a migration, a background job, a person with psql open at 2am. The database runs the trigger for all of them. That is the property nothing else on this page has, and it is the reason triggers exist.

CREATE TABLE customer_audit (
  id          bigserial PRIMARY KEY,
  customer_id bigint      NOT NULL,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  changed_by  text        NOT NULL,
  operation   text        NOT NULL,
  old_row     jsonb,
  new_row     jsonb
);

CREATE FUNCTION audit_customer() RETURNS trigger                 -- (1)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO customer_audit (customer_id, changed_by, operation, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),                                    -- (2)
    current_user,                                                -- (3)
    TG_OP,                                                       -- (4)
    to_jsonb(OLD),                                               -- (5)
    to_jsonb(NEW)
  );
  RETURN NULL;                                                   -- (6)
END;
$$;

CREATE TRIGGER customers_audit
AFTER INSERT OR UPDATE OR DELETE ON customers                    -- (7)
FOR EACH ROW EXECUTE FUNCTION audit_customer();                  -- (8)

(1) A trigger function takes no arguments in its signature and returns the special type trigger. It gets everything it needs from variables the engine sets before calling it. (2) NEW is the row as it will be after the change; OLD is the row as it was before. On an INSERT there is no OLD, and on a DELETE there is no NEW, so COALESCE picks whichever exists. (3) current_user is the database role that ran the statement, which is exactly the "who" nobody could answer. (4) TG_OP is a string the engine sets to 'INSERT', 'UPDATE' or 'DELETE', so one function serves all three events. (5) to_jsonb(OLD) turns the whole row into a JSON object in one call, so the audit table does not need a column per audited column and keeps working when somebody adds one. (6) In an AFTER trigger the return value is ignored entirely — RETURN NULL is the conventional way of saying "nothing to hand back". Section 2 shows where the return value does matter enormously. (7) One trigger, three events. (8) FOR EACH ROW means the function runs once per affected row: an UPDATE touching 500 rows calls it 500 times.

Now every change is recorded, from every writer, forever.

1. The five choices in a trigger definition

Every trigger is a combination of five decisions, and knowing what each one costs is most of the skill.

When it fires: BEFORE, AFTER, or INSTEAD OF.

• BEFORE runs before the row is written and can change or cancel the write.
• AFTER runs once the row is written and any constraints have been checked. It cannot change the row, and it is the correct choice for anything that reacts to a change — auditing, queueing a notification, updating another table.
• INSTEAD OF only exists on views, and it replaces the write entirely with whatever you write. Section 5.

What it fires on: INSERT, UPDATE, DELETE, TRUNCATE. You may combine them with OR. UPDATE OF status narrows it to updates that mention that column.

How often: FOR EACH ROW or FOR EACH STATEMENT. A statement trigger runs once per statement no matter how many rows were touched — once even if zero rows were touched. A row trigger runs once per row. UPDATE orders SET status = 'x' over a million rows calls a row trigger a million times, and that is where trigger performance problems come from.

An optional WHEN condition that decides whether the function is called at all:

CREATE TRIGGER orders_shipped_notify
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status AND NEW.status = 'shipped')   -- (1)
EXECUTE FUNCTION queue_shipping_email();

(1) IS DISTINCT FROM rather than <>, because either side can be null and NULL <> 'shipped' is unknown, which is not true, which would skip the row — the exact bug from Chapter 7.1 section 3. The WHEN clause is evaluated by the engine itself, so a row that does not match never enters the function at all. On a hot table this is much cheaper than an IF at the top of the function, and it is free to add.

And the function to run, which is an ordinary function returning trigger, written as Chapter 7.2.8 describes.

2. What BEFORE can do that AFTER cannot

A BEFORE … FOR EACH ROW trigger's return value becomes the row that gets written. That gives it two powers.

It can modify the row on the way in. This is the standard updated_at implementation, and it is better than a default because a default only applies on insert.

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

CREATE TRIGGER orders_touch
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

(1) Assigning to a field of NEW changes what will be stored. (2) Returning NEW is what commits that change. Return OLD by mistake and the update quietly does nothing at all — the row is written back exactly as it was, no error, no clue.

It can cancel the write for that row by returning NULL.

IF NEW.total_minor < 0 THEN
  RETURN NULL;      -- this row is silently not inserted
END IF;

Silently is the operative word, and it is why this power is dangerous. The client is told the statement succeeded. INSERT … RETURNING gives back nothing for that row, and the row count is lower than the number of rows submitted, but nothing raises an error. Anybody debugging sees data that simply is not there. If a row should be rejected, raise an exception instead — RAISE EXCEPTION 'total cannot be negative' — which aborts the statement and tells the caller why. Returning NULL is for the narrow case where skipping is genuinely the intended behaviour, such as a partitioning router that has already redirected the row elsewhere.

AFTER row triggers cannot do either of these. The row is already written, and the return value is discarded. In exchange, an AFTER trigger sees the row as it finally is — including values set by other BEFORE triggers, by defaults, and by generated columns — and it runs after foreign keys and check constraints have passed, so it never reacts to a change that is about to be rolled back for a constraint violation. Reacting to a change is AFTER. Shaping a change is BEFORE.

3. Statement triggers and transition tables

A row trigger that runs a million times is a million function calls. When the work can be done in bulk, a statement-level trigger with transition tables does it in one.

CREATE FUNCTION audit_orders_bulk() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
  SELECT n.id, o.status, n.status, now()
  FROM new_rows n JOIN old_rows o ON o.id = n.id        -- (1)
  WHERE n.status IS DISTINCT FROM o.status;             -- (2)
  RETURN NULL;
END;
$$;

CREATE TRIGGER orders_audit_bulk
AFTER UPDATE ON orders
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows -- (3)
FOR EACH STATEMENT EXECUTE FUNCTION audit_orders_bulk();

(1) old_rows and new_rows are real, queryable relations holding every row the statement touched, in its before and after state. You join them like tables, because that is what they are. (2) Only genuinely changed statuses get audited. (3) REFERENCING … TABLE AS is what creates them, and it is available on AFTER triggers from PostgreSQL 10 onwards.

One INSERT … SELECT replaces a million individual inserts. On a bulk update this is often ten to a hundred times faster than the row-level version. The trade is that you cannot modify rows here — statement triggers have no NEW record to change — so this shape is for reacting, never for shaping.

A statement trigger with no transition tables cannot see the rows at all. It knows the statement ran and nothing more, which makes it useful for coarse work: refreshing a cache key, bumping a version number, recording that the table was touched.

4. Firing order, and the parts that surprise people

When several triggers exist for the same event, they fire in alphabetical order by trigger name. Not creation order. Not definition order. Alphabetical, which means renaming a trigger changes program behaviour, and it means the common convention of prefixing names with numbers — 10_normalise, 20_validate, 30_audit — is not fussiness but the only way to control the sequence.

The full order for one row is: all BEFORE row triggers in name order, then the row is written, then constraints are checked, then all AFTER row triggers in name order. Statement triggers wrap the whole thing on the outside.

Four behaviours that catch people, each worth knowing before it bites.

TRUNCATE does not fire row triggers. It fires statement-level triggers only, because it never touches individual rows — it discards whole files. So a delete-auditing trigger records nothing when somebody truncates the table, which is the exact moment you would most want a record. If that matters, revoke TRUNCATE or add a statement trigger for it.

COPY does fire row triggers. A bulk load of ten million rows through COPY calls your BEFORE INSERT trigger ten million times, and this is a frequent and very unwelcome surprise during a data migration. Dropping the trigger, loading, and recreating it is standard practice, and it is only safe if you can also apply whatever the trigger would have done.

Foreign keys are triggers underneath. PostgreSQL implements referential integrity with internal AFTER triggers, which you can see in pg_trigger. This is why a foreign key check can be DEFERRABLE — it is a trigger whose firing is postponed to commit time — and it explains why very wide fan-out foreign keys have a per-row cost.

Triggers do not fire on a logical replication subscriber by default. The replica applies changes with session_replication_role set to replica, which disables ordinary user triggers. So an audit trigger on the primary produces audit rows on the primary that then replicate normally, while a trigger that only exists on the replica does nothing at all. If you genuinely want a trigger to run on replicated changes, it must be marked ALTER TABLE … ENABLE ALWAYS TRIGGER. The same switch is how bulk-loading tools skip your triggers, which is worth knowing when data arrives and your trigger apparently did not run.

5. INSTEAD OF: making a view writable

Chapter 7.2.6 said a view is only automatically updatable when it is simple, and that anything more complex needs an INSTEAD OF trigger. This is that.

CREATE VIEW customer_profile AS
SELECT c.id, c.email, a.line1, a.city
FROM customers c LEFT JOIN addresses a ON a.customer_id = c.id;

CREATE FUNCTION save_customer_profile() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE customers SET email = NEW.email WHERE id = NEW.id;      -- (1)

  INSERT INTO addresses (customer_id, line1, city)
  VALUES (NEW.id, NEW.line1, NEW.city)
  ON CONFLICT (customer_id) DO UPDATE                            -- (2)
    SET line1 = EXCLUDED.line1, city = EXCLUDED.city;

  RETURN NEW;                                                    -- (3)
END;
$$;

CREATE TRIGGER customer_profile_save
INSTEAD OF UPDATE ON customer_profile                            -- (4)
FOR EACH ROW EXECUTE FUNCTION save_customer_profile();

(1) One update against the customers table. (2) An upsert against addresses, so a customer who had no address row gets one. EXCLUDED refers to the row that the insert was trying to add. (3) Returning NEW reports success to the caller and populates RETURNING. (4) INSTEAD OF triggers exist only on views and only as FOR EACH ROW. The write against the view no longer happens at all — your function is the write.

This is the one place where a trigger's action-at-a-distance is not a problem, because the view has no behaviour of its own to be surprised by.

6. Where triggers go wrong

They are invisible. A developer reads the INSERT statement, reads the application code around it, and has the complete picture — except for the four hundred lines of PL/pgSQL that also ran. Nothing in the query text hints that a trigger exists, and a bug whose cause is a trigger is genuinely hard to find if you do not already suspect one. The first move when a write behaves impossibly is \d tablename in psql, which lists the triggers.

They can recurse. A trigger on orders that updates orders fires itself. PostgreSQL does not stop you, and the result is either a runaway or a stack depth error. Guard it:

IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF;   -- already inside a trigger

Or, better, avoid the shape: a BEFORE trigger that modifies NEW does not need to run an UPDATE at all, because assigning to NEW is the update.

They serialise writes when they maintain a counter. This one causes real outages, so it is worth walking through.

-- Looks reasonable. Is a scalability trap.
CREATE FUNCTION bump_line_count() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders SET line_count = line_count + 1 WHERE id = NEW.order_id;
  RETURN NULL;
END;
$$;

Every insert into order_lines now updates the parent order row, which locks it until the transaction commits. Two sessions adding lines to the same order queue up behind each other, which is tolerable. But two sessions adding lines to two different orders in the opposite sequence deadlock, and Chapter 7.4.2 explains exactly why. The general rule: a trigger that writes to a shared row turns independent transactions into contending ones, and the contention is invisible in the application code that caused it.

They add cost to every single write. A trigger firing on a table taking 5,000 inserts a second runs 5,000 times a second, and any query inside it runs 5,000 times a second too. That is fine for an insert into an audit table with no indexes beyond its key. It is not fine for a trigger that joins three tables to compute something.

An exception inside a trigger aborts the whole statement. Your audit trigger fails because the audit table ran out of disk, and now customers cannot be updated. A trigger you consider optional is not optional to the transaction, so anything that can fail independently — writing to a queue, calling out to another system — should be recorded as a row for a separate worker to pick up, not attempted inside the trigger.

7. Trigger, constraint, generated column, or application code?

This is the comparison the whole page has been building towards, because "should this be a trigger?" is almost always really "which of these five tools is it?".

Tool	Runs when	Can change data	Applies to
DEFAULT	Insert, if column omitted	Sets one column	All writers
Generated column	Every write	Computes one column	All writers
CHECK / FOREIGN KEY	Every write	No — accepts or rejects	All writers
Trigger	Every write	Anything	All writers
Application code	When called	Anything	That code path only

Read that table by the last column first. The top four apply to every writer, including the migration and the person at 2am. Application code applies only to the path that contains it. That is the whole reason to reach into the database at all, and it is the same argument as constraints in Chapter 7.1 section 9.

Then work down the list and stop at the first tool that does the job, because each row is more powerful and harder to see than the one above it.

DEFAULT handles "a value if none is given". created_at timestamptz NOT NULL DEFAULT now() needs no trigger. It only applies on insert, which is why updated_at cannot use it.

A generated column handles "this column is computed from other columns in the same row". This is the one that made a whole category of triggers obsolete in PostgreSQL 12, and many codebases still carry the trigger version.

ALTER TABLE order_lines
  ADD COLUMN line_total_minor bigint
  GENERATED ALWAYS AS (qty * unit_price_minor) STORED;    -- (1)

(1) STORED means the value is computed on write and kept on disk, so it can be indexed and read like any column. It cannot be written to, it cannot drift out of step with its inputs, and it costs a multiplication rather than a function call. A trigger doing this job is strictly worse in every respect. The limitation is that the expression may only use columns of the same row and must be immutable in the sense of Chapter 7.2.8 section 3 — so a generated column cannot count child rows or look at another table.

A CHECK constraint or a FOREIGN KEY handles "this must be true". CHECK (qty > 0) is checked on every write, is visible in the table definition, is understood by the planner, and costs almost nothing. A trigger that only validates is a slower, invisible constraint, and the only reason to write one is a rule a CHECK cannot express — one that involves other rows or other tables. CHECK may only look at the row in front of it.

A trigger handles the rest, and the honest list of what "the rest" is:

• Reacting to a change: auditing, queueing work, recording history.
• A rule that spans rows or tables: "an order may not have more than 50 lines", which a CHECK cannot see.
• Maintaining a denormalised value that a generated column cannot compute, with the contention warning from section 6.
• Making a complex view writable, with INSTEAD OF.
• Normalising input from writers you do not control.

Application code handles everything that is about the business rather than the data, everything that changes often, and everything that needs to talk to the outside world. It is readable, reviewable, observable, and it is where a rule belongs unless there is a specific reason it must hold for every writer.

The rule of thumb that gets it right most of the time: if you cannot explain why the rule must hold even when the application is not involved, it is not a trigger.

8. How other engines differ, because assumptions do not travel

SQL Server triggers are statement-level, always. There is no FOR EACH ROW. A trigger receives two pseudo-tables, inserted and deleted, holding all affected rows — the same idea as PostgreSQL's transition tables, but as the only option. The classic production bug is a SQL Server trigger written as though inserted holds exactly one row, using SELECT @id = id FROM inserted. It works perfectly for every single-row insert and silently processes only one arbitrary row the first time somebody does a bulk insert.

MySQL triggers are row-level, always. There are no statement triggers and no transition tables, so bulk work has no fast path. Before 5.7 you could only have one trigger per event per table; since 5.7 you can have several and order them with FOLLOWS and PRECEDES rather than by name.

Oracle has both, plus compound triggers, plus the mutating table error: a row trigger may not query the table it is firing on, because that table is mid-change and the read would be inconsistent. Working around it is a well-known piece of Oracle lore and it does not arise in PostgreSQL, which allows the read.

What the interviewer will push on

"What is a trigger and when would you use one?" Code the database runs on every write to a table, from every writer. Give the two honest uses first — auditing that cannot be bypassed, and a rule spanning rows that a CHECK cannot express — then immediately name the cost, which is that nothing in the query text says a trigger exists. Answering only "for auditing" without the invisibility cost reads as never having debugged one.

"BEFORE or AFTER?" BEFORE can change the row — its return value is what gets written — and can cancel it by returning NULL. AFTER sees the final row after constraints passed and cannot change anything. Shaping is BEFORE, reacting is AFTER. Then volunteer that cancelling by returning NULL is silent, so a rejection should raise an exception instead.

"Trigger or constraint?" Constraint whenever it is expressible: visible in the table definition, understood by the planner, near-free. A trigger is for what a CHECK cannot see, because CHECK may only look at the row in front of it. Then add generated columns as the third answer — since PostgreSQL 12 they replaced an entire category of "keep this computed column in sync" triggers.

"What is the difference between a trigger and a stored procedure?" A procedure is called explicitly by someone; a trigger is invoked by the database in response to a write and can never be called directly. The tell is going further: a trigger runs inside the writer's transaction, so if it fails the write fails, which is why an audit trigger that cannot write blocks all updates to the table.

"Your trigger maintains a count on the parent row and throughput collapsed. Why?" Every child insert now updates and locks the same parent row, so transactions that were independent are contending, and two of them touching two parents in opposite order deadlock. The fix is either an insert-only tally that is summed later, or accepting the recomputation. Naming deadlock rather than just "it is slow" is the difference.

"Anything you would watch out for with triggers on a bulk load?" COPY fires row triggers, so a ten-million-row load calls them ten million times. And TRUNCATE fires only statement triggers, so a delete-audit trigger records nothing at the exact moment you most want a record. Both are specific, both are commonly discovered the hard way.

One thing to volunteer: mention that triggers fire in alphabetical order by trigger name, not creation order, so renaming a trigger can change behaviour and numeric name prefixes are the only way to control the sequence. Almost nobody knows this until two triggers on one table start fighting.

Recall

• A trigger is code the database runs on every write to a table, from every writer — application, migration, or a person typing at 2am. That property is the entire reason to use one.
• Five choices: BEFORE / AFTER / INSTEAD OF, the events, FOR EACH ROW or FOR EACH STATEMENT, an optional WHEN condition evaluated by the engine, and the function.
• BEFORE row triggers: the returned row is what gets written. Modify NEW to shape it, return NULL to skip it silently — which is why a rejection should RAISE EXCEPTION instead. AFTER cannot change anything and sees the final row after constraints passed.
• FOR EACH STATEMENT with REFERENCING OLD TABLE / NEW TABLE gives queryable transition tables, turning a million per-row calls into one INSERT … SELECT.
• Triggers fire in alphabetical order by name, not creation order. TRUNCATE fires only statement triggers, so delete-audit triggers miss it. COPY does fire row triggers. Foreign keys are internal triggers. Logical replication apply skips user triggers unless ENABLE ALWAYS.
• INSTEAD OF exists only on views and replaces the write entirely — the way to make a complex view writable.
• Dangers: invisible from the query text; recursion (pg_trigger_depth()); a trigger updating a shared parent row turns independent transactions into contending ones and deadlocks; a cost on every write; and an exception inside a trigger aborts the caller's statement.
• Order of preference: DEFAULT → generated column → CHECK/FOREIGN KEY → trigger → application code. Stop at the first that works; each step down is more powerful and harder to see.
• A generated column (GENERATED ALWAYS AS (…) STORED, PostgreSQL 12+) replaced a whole category of triggers. It cannot drift, cannot be written to, and costs an expression — but only sees its own row.
• SQL Server triggers are statement-level only (inserted/deleted tables), which is why code written as though one row was affected breaks on bulk writes. MySQL is row-level only, with no transition tables.

Self-test: What happens if a BEFORE UPDATE trigger returns OLD? · Why should a validation trigger raise rather than return NULL? · Why does a counter-maintaining trigger cause deadlocks? · Which statement silently bypasses your delete-audit trigger? · When does a generated column beat a trigger, and when can it not be used? · In what order do three triggers on one table fire?

Next: 7.3.1 goes under all of this — what a row physically is on disk, how pages and the buffer pool work, and why the answer to "where is my data" is never "in a file, one row per line".