TopolearnTriggers enforce rules and maintain derived data for every writer; NOTIFY pushes changes to apps.
A trigger runs a function automatically when rows are inserted, updated, deleted or truncated. The function is written as RETURNS trigger and sees the special variables NEW and OLD (the row after and before), TG_OP ('INSERT', 'UPDATE'...), and TG_TABLE_NAME.
- BEFORE ROW triggers can modify
NEWbefore it is written (setupdated_at, normalise values) or skip the row by returningNULL. - AFTER ROW triggers see the final row and are used for audit logs, maintaining counters or summary tables, and notifications.
- Statement-level triggers (
FOR EACH STATEMENT) run once per statement; with transition tables (REFERENCING NEW TABLE AS ...) they can process all changed rows in one set-based step, which is much faster for bulk changes. WHEN (OLD.status IS DISTINCT FROM NEW.status)limits when a trigger fires.
Triggers run inside the same transaction, so they're consistent — and their cost is added to every write. Keep them small, avoid long chains of triggers firing triggers, and document them: hidden behaviour surprises developers.
LISTEN/NOTIFY is a lightweight pub/sub channel: NOTIFY channel, 'payload' (or pg_notify()) inside a trigger delivers a message to every session that ran LISTEN channel, but only when the transaction commits. Good for cache invalidation; not a durable queue — messages to disconnected listeners are lost.
Example
CREATE TABLE task (id int PRIMARY KEY, title text, status text,
updated_at timestamptz);
CREATE FUNCTION task_touch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
IF TG_OP = 'UPDATE' AND OLD.status IS DISTINCT FROM NEW.status THEN
PERFORM pg_notify('task_events',
json_build_object('id', NEW.id, 'status', NEW.status)::text);
END IF;
RETURN NEW; -- BEFORE trigger: the row to write
END $$;
CREATE TRIGGER task_touch BEFORE INSERT OR UPDATE ON task
FOR EACH ROW EXECUTE FUNCTION task_touch();
LISTEN task_events;
INSERT INTO task (id, title, status) VALUES (1, 'Write docs', 'open');
UPDATE task SET status = 'done' WHERE id = 1;
-- Asynchronous notification "task_events" with payload {"id" : 1, "status" : "done"}
SELECT updated_at IS NOT NULL FROM task; -- tRemember
- BEFORE ROW triggers can change NEW; AFTER triggers react to final rows
- Statement triggers with transition tables are efficient for bulk changes
- NOTIFY is delivered on commit and isn't durable
Watch out for
- Putting slow work (HTTP calls, heavy queries) in triggers and slowing every write.