Postgres Triggers: Automatic Actions on Insert, Update and Delete
A trigger runs a function automatically when rows change. How to write trigger functions in PL/pgSQL, BEFORE vs AFTER and row vs statement triggers, practical examples — updated_at, audit logs, denormalised counters, validation — and the pitfalls that make triggers hard to debug.
A trigger tells Postgres: "whenever rows in this table are inserted, updated or deleted, run this function". The logic lives in the database, so it runs no matter which app, script or admin tool made the change.
The two parts
- A trigger function (usually PL/pgSQL) that returns
trigger. - A trigger attaching it to a table and event.
Example 1: keep updated_at current
The most common trigger:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
CREATE TRIGGER orders_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
NEWis the row as it will be saved;OLDis the row before the change (for UPDATE/DELETE).BEFOREtriggers can modifyNEWbefore it's written.- Returning
NEWlets the change proceed; returningNULLfrom a BEFORE row trigger skips it.
BEFORE vs AFTER, row vs statement
| Use for | |
|---|---|
BEFORE ... FOR EACH ROW |
Changing or validating the row being written |
AFTER ... FOR EACH ROW |
Reacting to a change: audit logs, counters, notifications |
FOR EACH STATEMENT |
Once per statement regardless of row count (e.g. refresh something once) |
Add WHEN (...) to fire only when relevant:
CREATE TRIGGER orders_status_changed
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_status_change();
Example 2: an audit log
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name text,
row_id bigint,
action text,
old_data jsonb,
new_data jsonb,
changed_at timestamptz DEFAULT now()
);
CREATE OR REPLACE FUNCTION audit()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO audit_log (table_name, row_id, action, old_data, new_data)
VALUES (TG_TABLE_NAME, COALESCE(NEW.id, OLD.id), TG_OP,
to_jsonb(OLD), to_jsonb(NEW));
RETURN NULL; -- return value ignored for AFTER triggers
END;
$$;
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit();
TG_OP and TG_TABLE_NAME tell the function what fired it. (Soft deletes and audit logs, Postgres JSONB)
Example 3: a denormalised counter
CREATE OR REPLACE FUNCTION bump_comment_count()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
END IF;
RETURN NULL;
END;
$$;
Fast reads of comment_count, at the cost of a write on posts per comment — which can become a lock hotspot on very popular rows. (Optimistic vs pessimistic locking)
Example 4: notifying your app
A trigger can call pg_notify('orders', NEW.id::text) so app processes listening with LISTEN orders react immediately — a lightweight event mechanism. For reliable event delivery, prefer the outbox pattern. (Transactional outbox pattern)
When triggers are the right tool
- Invariants that must hold regardless of which code writes (timestamps, audit trails).
- Data integrity rules too complex for a
CHECKconstraint. - Supabase and other "database as backend" setups, where the client writes directly and there's no app server in between. (What is Supabase?)
The pitfalls
- Hidden behaviour — someone reading your app code won't see that an update also writes three other tables. Document triggers, and keep them in migrations. (Database migrations explained)
- Performance — row triggers run per row; a bulk update of a million rows runs the function a million times.
- Cascades — triggers that update tables with triggers can chain unexpectedly, or loop.
- Debugging — errors surface as failures of the original statement, with a context line naming the trigger.
- Business logic in two places — prefer app code for workflows (send email, call APIs); triggers can't (and shouldn't) call external services.
- Constraints first —
NOT NULL,CHECK,UNIQUEand foreign keys are simpler and faster where they suffice. (ON DELETE CASCADE)
Managing triggers
\dS orders -- psql: shows triggers on a table
ALTER TABLE orders DISABLE TRIGGER orders_audit; -- e.g. during a bulk backfill
DROP TRIGGER orders_audit ON orders;
EasySpawn gives each app its own Postgres database, with Claude Code to write, test and document triggers in your migrations — and to find the one that's quietly slowing your bulk updates. See how it works or join the waitlist.
Related: Soft Deletes and Audit Logs · The Transactional Outbox Pattern · Postgres Materialized Views · Database Transactions Explained · Postgres Generated Columns · Postgres LISTEN/NOTIFY
Keep reading
UUIDv7 Explained: Time-Ordered IDs and Why Postgres 18 Added uuidv7()
UUIDv7 is a UUID that starts with a millisecond timestamp, so new IDs sort in creation order. Why that makes database indexes faster than random UUIDv4, the format in detail, generating them in Postgres 18, Node.js and Python, what the timestamp leaks, and when to still use v4 or bigint.
SQLite WAL Mode: Faster Writes and Readers That Don't Block
Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.