Engineer row and statement triggers, transition relations, deferred constraint triggers, compact auditing, and controlled recursion while keeping hidden workflow ordering understandable.
Triggers, Transition Tables, Deferred Behavior, Audit Patterns, and Recursion Risks
Engineer row and statement triggers, transition relations, deferred constraint triggers, compact auditing, and controlled recursion while keeping hidden workflow ordering understandable.
Learning outcomes
Triggers execute implicitly when table/view events occur, which makes them useful for invariants and mechanically local auditing—and dangerous when they hide broad business workflows. This lesson makes timing and scope observable: BEFORE versus AFTER versus INSTEAD OF, row versus statement, transition relations, deferred constraint triggers, and recursion depth.
Compare BEFORE, AFTER, and INSTEAD OF trigger timing and row/statement scope.
Use WHEN conditions to avoid unnecessary trigger execution.
Use transition relations to audit all rows changed by one statement without one audit INSERT per row.
Demonstrate a DEFERRABLE constraint trigger and explain why a simple CHECK is preferable when it can express the invariant.
Create a bounded recursion example with pg_trigger_depth(), then repair it by modifying NEW in a BEFORE trigger.
1. Trigger catalog and privilege model
To create a trigger, the creator needs
TRIGGER privilege on the target relation and
EXECUTE privilege on the trigger function. A
trigger function takes no declared arguments and returns the
special trigger type; PL/pgSQL exposes
NEW, OLD, TG_OP,
TG_WHEN, TG_LEVEL, and related context
variables.
DROP VIEW IF EXISTS app.ch18_work_order_queue CASCADE;DROP TABLE IF EXISTS app.ch18_trigger_audit CASCADE;DROP TABLE IF EXISTS app.ch18_trigger_order CASCADE;CREATE TABLE app.ch18_trigger_order ( work_order_id bigint PRIMARY KEY, status text NOT NULL CHECK (status IN ('queued','assigned','completed')), technician_note text, changed_at timestamptz NOT NULL DEFAULT clock_timestamp(), closed_at timestamptz);INSERT INTO app.ch18_trigger_order VALUES(18301,'queued','awaiting assignment',clock_timestamp(),NULL),(18302,'assigned','on site',clock_timestamp(),NULL),(18303,'assigned','parts ordered',clock_timestamp(),NULL);CREATE TABLE app.ch18_trigger_audit ( audit_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, source text NOT NULL, operation text NOT NULL, affected_rows integer NOT NULL, payload jsonb NOT NULL, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp());2. BEFORE ROW is the natural place to adjust NEW
CREATE OR REPLACE FUNCTION app.ch18_touch_order()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN NEW.changed_at := clock_timestamp(); IF NEW.status = 'completed' AND NEW.closed_at IS NULL THEN NEW.closed_at := clock_timestamp(); END IF; RETURN NEW;END$$;CREATE TRIGGER a_ch18_touch_orderBEFORE UPDATE ON app.ch18_trigger_orderFOR EACH ROWWHEN (OLD.* IS DISTINCT FROM NEW.*)EXECUTE FUNCTION app.ch18_touch_order();
A BEFORE ROW trigger can replace NEW or return NULL
to suppress the row operation. It runs before constraints and
the physical row modification. Here it keeps timestamps local to
the same row mutation instead of issuing another UPDATE after
the row was already changed.
3. AFTER STATEMENT transition tables see the whole change set
CREATE OR REPLACE FUNCTION app.ch18_audit_order_update_statement()RETURNS triggerLANGUAGE plpgsqlAS $$DECLARE v_count integer; v_payload jsonb;BEGIN SELECT count(*), jsonb_agg( jsonb_build_object( 'id', n.work_order_id, 'old_status', o.status, 'new_status', n.status ) ORDER BY n.work_order_id ) INTO v_count, v_payload FROM old_rows AS o JOIN new_rows AS n USING (work_order_id) WHERE o.* IS DISTINCT FROM n.*; IF v_count > 0 THEN INSERT INTO app.ch18_trigger_audit(source,operation,affected_rows,payload) VALUES ('app.ch18_trigger_order','UPDATE',v_count,coalesce(v_payload,'[]'::jsonb)); END IF; RETURN NULL;END$$;CREATE TRIGGER m_ch18_audit_order_updateAFTER UPDATE ON app.ch18_trigger_orderREFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rowsFOR EACH STATEMENTEXECUTE FUNCTION app.ch18_audit_order_update_statement();UPDATE app.ch18_trigger_orderSET status = 'completed'WHERE work_order_id IN (18302,18303);SELECT affected_rows, payloadFROM app.ch18_trigger_auditORDER BY audit_id DESCLIMIT 1;
Transition relations are available only for eligible
AFTER triggers on plain tables, not constraint
triggers. They let one trigger function reason about the set
modified by the statement. That is often cheaper and easier to
audit than one INSERT per changed row.
4. AFTER ROW WHEN can avoid queuing irrelevant work
CREATE OR REPLACE FUNCTION app.ch18_audit_status_row()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN INSERT INTO app.ch18_trigger_audit(source,operation,affected_rows,payload) VALUES ( 'app.ch18_trigger_order', 'STATUS_CHANGE', 1, jsonb_build_object( 'id', NEW.work_order_id, 'from', OLD.status, 'to', NEW.status ) ); RETURN NULL;END$$;CREATE TRIGGER n_ch18_audit_status_rowAFTER UPDATE ON app.ch18_trigger_orderFOR EACH ROWWHEN (OLD.status IS DISTINCT FROM NEW.status)EXECUTE FUNCTION app.ch18_audit_status_row();For an AFTER trigger, PostgreSQL evaluates the WHEN condition just after the row operation and only queues the trigger event when it is true. That can avoid unnecessary end-of-statement work. If several triggers have the same timing/event on one relation, PostgreSQL fires them alphabetically by trigger name; do not build an undocumented workflow that depends on accidental names.
5. INSTEAD OF triggers belong to views
CREATE VIEW app.ch18_work_order_queue ASSELECT work_order_id, technician_noteFROM app.ch18_trigger_orderWHERE status = 'queued';CREATE OR REPLACE FUNCTION app.ch18_queue_insert()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN INSERT INTO app.ch18_trigger_order(work_order_id,status,technician_note) VALUES (NEW.work_order_id,'queued',NEW.technician_note); RETURN NEW;END$$;CREATE TRIGGER ch18_queue_insertINSTEAD OF INSERT ON app.ch18_work_order_queueFOR EACH ROWEXECUTE FUNCTION app.ch18_queue_insert();INSERT INTO app.ch18_work_order_queue(work_order_id,technician_note)VALUES (18304,'created through queue view');
INSTEAD OF row triggers are for views and replace
the requested INSERT/UPDATE/DELETE action with trigger logic.
They are not available on ordinary tables. If a view is
automatically updatable without a trigger, prefer the simpler
built-in behavior unless the view API genuinely requires custom
mapping.
6. Deferred constraint triggers: observe the timing, then choose the simplest invariant
Constraint triggers must be AFTER ROW triggers on
plain tables. They can be DEFERRABLE and run at statement end or
transaction end, controlled by SET CONSTRAINTS.
This lab intentionally uses an invariant that a CHECK could
express, so the trigger is pedagogical rather than recommended
design.
CREATE OR REPLACE FUNCTION app.ch18_completed_requires_closed_at()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN IF NEW.status = 'completed' AND NEW.closed_at IS NULL THEN RAISE EXCEPTION USING ERRCODE = '23514', MESSAGE = 'completed work order requires closed_at', CONSTRAINT = 'ch18_completed_requires_closed_at'; END IF; RETURN NULL;END$$;CREATE CONSTRAINT TRIGGER ch18_completed_requires_closed_atAFTER INSERT OR UPDATE ON app.ch18_trigger_orderDEFERRABLE INITIALLY DEFERREDFOR EACH ROWEXECUTE FUNCTION app.ch18_completed_requires_closed_at();BEGIN;INSERT INTO app.ch18_trigger_order(work_order_id,status,technician_note,closed_at)VALUES (18305,'completed','temporary incomplete row',NULL);-- The row exists inside this transaction until deferred checking is forced.SET CONSTRAINTS ch18_completed_requires_closed_at IMMEDIATE;-- Expected: 23514-style constraint violation; transaction becomes aborted.ROLLBACK;If the invariant is row-local, use a CHECK constraint instead: it is clearer, visible to tooling, and does not hide correctness in procedural code. Deferred constraint triggers are more appropriate when the invariant truly requires deferred cross-row/table evaluation and concurrency has been designed explicitly.
7. Controlled recursion: show the problem without crashing the session
An AFTER UPDATE trigger that issues another UPDATE on the same
row can recursively invoke itself. The deliberately bad function
below caps recursion using pg_trigger_depth() so
the lab is observable instead of infinite.
CREATE OR REPLACE FUNCTION app.ch18_recursive_touch_bad()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN RAISE NOTICE 'trigger depth=%', pg_trigger_depth(); IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF; UPDATE app.ch18_trigger_order SET changed_at = clock_timestamp() WHERE work_order_id = NEW.work_order_id; RETURN NULL;END$$;CREATE TRIGGER z_ch18_recursive_touch_badAFTER UPDATE ON app.ch18_trigger_orderFOR EACH ROWEXECUTE FUNCTION app.ch18_recursive_touch_bad();UPDATE app.ch18_trigger_orderSET technician_note = 'recursion demo'WHERE work_order_id = 18304;You should see depth 1 and then depth 2. The guard prevents unbounded recursion, but it does not make the design good: it still creates a second UPDATE, another MVCC row version, extra index/WAL work, and interaction with every other UPDATE trigger.
DROP TRIGGER z_ch18_recursive_touch_bad ON app.ch18_trigger_order;DROP FUNCTION app.ch18_recursive_touch_bad();-- The existing BEFORE trigger app.ch18_touch_order already changes NEW.changed_at-- without issuing a second UPDATE.8. Trigger inventory and deterministic tests
SELECT c.oid::regclass AS relation, t.tgname, t.tgenabled, pg_get_triggerdef(t.oid, true) AS definitionFROM pg_trigger AS tJOIN pg_class AS c ON c.oid = t.tgrelidWHERE c.oid IN ( 'app.ch18_trigger_order'::regclass, 'app.ch18_work_order_queue'::regclass) AND NOT t.tgisinternalORDER BY relation::text, t.tgname;Use triggers for local invariants, derived row state, and auditing that must apply to every writer. Keep broad workflows—network calls, multi-service orchestration, retries, notifications, or complex ordering—in an explicit application/job boundary where observability and ownership are clearer.
9. Checkpoint
Check your understanding
- When is a BEFORE ROW trigger preferable to an AFTER self-UPDATE?
- What do transition relations provide that NEW/OLD row variables do not?
- Where can INSTEAD OF triggers be defined?
- What makes a constraint trigger different from an ordinary trigger?
- Why is pg_trigger_depth() useful for diagnosis but not a substitute for fixing recursive design?
Review the answers
BEFORE can modify NEW without a second physical UPDATE. Transition relations expose the whole statement change set. INSTEAD OF row triggers are for views. Constraint triggers are AFTER ROW triggers that can be deferrable and controlled by SET CONSTRAINTS. pg_trigger_depth reveals nested trigger execution, but a depth guard still leaves hidden extra DML and should not replace a nonrecursive design.
Authoritative references
Routines, trigger timing, privileges, and planner promises are version-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.