A trigger makes PostgreSQL call a function automatically when a table changes. Write the trigger function first, declared RETURNS trigger, then attach it: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. The classic use is keeping an updated_at column current:
Only post 1 was updated, so only it has a timestamp. NEW is the row about to be written; a BEFORE trigger can change it and must return it. You can attach the same function to every table that has an updated_at column.
BEFORE vs AFTER vs INSTEAD OF
| Timing | Runs | Typical use |
|---|---|---|
BEFORE | before the row is written | change or validate NEW, or skip the row |
AFTER | after the row is written | write to other tables: audit logs, counters |
INSTEAD OF | in place of the change, on views only | make a view writable |
FOR EACH ROW runs the function once per affected row. FOR EACH STATEMENT (the default) runs it once per statement, even if no row changed. In a row trigger, NEW holds the new row (INSERT, UPDATE) and OLD the previous one (UPDATE, DELETE). TG_OP names the operation that fired it and TG_TABLE_NAME the table.
The return value of a BEFORE row trigger decides what happens: return NEW (changed or not) to go ahead, or NULL to skip the row without an error. An AFTER trigger's return value is ignored, so it returns NULL by convention.
Write an audit log with an AFTER trigger
The audit table records two INSERTs, an UPDATE from 10 to 12 and a DELETE of the product priced 20. OLD is NULL in an insert and NEW is NULL in a delete, hence the coalesce. To store whole rows, put to_jsonb(OLD) and to_jsonb(NEW) in a jsonb column.
Block or skip a change
A BEFORE trigger can reject a change with RAISE EXCEPTION, which aborts the statement, or skip a row by returning NULL:
The DELETE removed the draft and silently kept the paid invoice. A BEFORE DELETE trigger returns OLD to let the deletion go ahead. To make the whole statement fail instead, replace RETURN NULL with RAISE EXCEPTION 'invoice % is paid', OLD.id;. An error is easier to debug than a row that quietly stays.
Fire only when a column changes
UPDATE OF column limits a trigger to updates that set that column. A WHEN condition skips the function call when it is false. IS DISTINCT FROM treats NULL as a value, so it also catches changes to or from NULL:
The first update sets the same price, so the WHEN is false and the counter stays at 0. The second changes it, and price_changed ends at 1.
List, disable and drop triggers
-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;
ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;
In psql, \d products lists a table's triggers. CREATE OR REPLACE TRIGGER exists since PostgreSQL 14, and EXECUTE FUNCTION since PostgreSQL 11; older code writes EXECUTE PROCEDURE, which still works. A trigger that writes to its own table can fire itself again, so guard against loops (pg_trigger_depth() returns the nesting level). The trigger functions chapter lists every TG_ variable, and functions and procedures covers PL/pgSQL itself.
Frequently Asked Questions
Can a PostgreSQL trigger run without a function?
No. CREATE TRIGGER always calls a function declared RETURNS trigger. Write the function first, usually in PL/pgSQL, then attach it.
In what order do triggers on the same table fire?
Triggers of the same kind on the same event fire in alphabetical order of their names. Prefix names with numbers or letters if the order matters.
Does a trigger error roll back the statement?
Yes. An error raised in a trigger, for example with RAISE EXCEPTION, aborts the statement that fired it, and the whole transaction unless you catch it with a savepoint.
Do triggers fire on TRUNCATE?
Row-level DELETE triggers do not. Create a statement-level trigger with BEFORE TRUNCATE or AFTER TRUNCATE to react to it.