SQL Triggers: BEFORE/AFTER, NEW/OLD, and the updated_at Pattern

A trigger is a function the database fires automatically on INSERT, UPDATE, or DELETE against a table. It lets you attach logic directly to the write itself: fill in a housekeeping column, record a change history, or reject an invalid operation, no matter which client ran the query.

BEFORE/AFTER and ROW/STATEMENT

A trigger has two independent axes. Timing: BEFORE fires ahead of the write and can alter or cancel the row, AFTER fires once the row is already committed to the table. Granularity: FOR EACH ROW runs per affected row, FOR EACH STATEMENT runs once for the whole query.

• BEFORE ... FOR EACH ROW for editing values before they land (normalization, auto-columns).
• AFTER ... FOR EACH ROW for side effects: auditing, updating related tables.
• AFTER ... FOR EACH STATEMENT for aggregates or checks over the full statement.

In PostgreSQL a trigger is always two pieces: a plpgsql function returning type trigger, and the CREATE TRIGGER object that wires it onto an event.

NEW, OLD, and what to return

Inside a row-level function two special records are available: NEW is the incoming row state (INSERT/UPDATE), OLD is the prior state (UPDATE/DELETE). The function's return value matters in a BEFORE trigger:

• return NEW so the write proceeds, possibly with your edits;
• return OLD (for DELETE) so the delete proceeds;
• return NULL to silently skip the operation for that row.

CREATE FUNCTION normalize_user() RETURNS trigger AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  IF NEW.country IS NULL THEN
    NEW.country := 'US';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_normalize
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION normalize_user();

In an AFTER trigger the return value is ignored, though people still write RETURN NEW; out of habit. Mutating NEW only has an effect in BEFORE: after the write, edits no longer apply.

The auto-updated_at pattern

The most common and harmless use: automatically refresh a timestamp on every row change. One function serves any table that has an updated_at column.

ALTER TABLE notes ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

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

CREATE TRIGGER touch BEFORE UPDATE ON notes
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

It must be BEFORE: the value has to be set before the row is written. The application no longer needs to remember updated_at, so the column is always honest.

Audit logging

An AFTER trigger is the natural fit for a change log: the original operation has already passed its checks, and we simply append a row to a history table. We capture the full old and new state as jsonb.

CREATE TABLE orders_audit (
  id         bigserial PRIMARY KEY,
  order_id   bigint,
  action     text,
  old_row    jsonb,
  new_row    jsonb,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION log_order_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit (order_id, action, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit_trg
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION log_order_change();

The TG_OP variable holds the operation type (INSERT, UPDATE, DELETE), so one function covers all three cases. Returning NULL is safe in an AFTER trigger.

When triggers hurt

Triggers are powerful, but they are hidden logic: a developer reads an UPDATE and never sees that three more inserts ran behind it. Hence a few rules:

• Hidden behavior. Trigger logic is easy to miss during debugging and review. Keep it small and predictable.
• Performance. A row-level trigger runs per row: a bulk UPDATE over a million rows means a million function calls.
• Cascades and loops. A trigger that writes to another table can wake that table's triggers. Recursion and deadlocks are easy to stumble into.

Gotcha: when several row triggers fire on the same event in PostgreSQL, they run in alphabetical order of name. Do not rely on a "logical" order, name them deliberately.

Engine differences:

• MySQL uses no separate function: the body goes straight into CREATE TRIGGER ... FOR EACH ROW BEGIN ... END, and instead of NEW.col := you assign with SET NEW.col = .... MySQL has no statement-level triggers.
• ClickHouse has no classic DML triggers; similar needs are met with materialized views that react to inserts.