Postgres Triggers: Automatic Actions on Insert, Update and Delete

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

1. A trigger function (usually PL/pgSQL) that returns trigger.
2. 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();

• NEW is the row as it will be saved; OLD is the row before the change (for UPDATE/DELETE).
• BEFORE triggers can modify NEW before it's written.
• Returning NEW lets the change proceed; returning NULL from 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 CHECK constraint.
• 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, UNIQUE and 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