Triggers, Audit Trails, and Event-Driven Patterns

Triggers let PostgreSQL run server-side code automatically when table data changes. In this lesson, the useful outcome is narrow and concrete: record who changed an order, keep before-and-after row images for audit review, and enqueue a transactional event when an order becomes paid. The key benefit is atomicity. If the row change commits, the audit row and outbox row commit with it; if the transaction rolls back, the side effects roll back too.

Where Triggers Fit

A trigger is attached to a table, view, foreign table, or event such as a schema change. This chapter focuses on DML triggers on tables: INSERT, UPDATE, DELETE, and TRUNCATE. PostgreSQL invokes a trigger function through the executor, not through application code. That means every client path sees the same behavior: an admin psql session, a background worker, a web application, and a data migration all fire the trigger unless the operation or trigger is deliberately disabled.

The common audit pattern stores OLD and NEW row values. OLD is populated for updates and deletes. NEW is populated for inserts and updates. A trigger function also receives metadata such as TG_OP, TG_TABLE_NAME, TG_WHEN, and TG_LEVEL. These variables are why one function can handle several operations without hard-coding a different function for each statement.

Trigger Timing and Row Flow

PostgreSQL has two major timing choices. A BEFORE row trigger runs before the row is written. It may return a modified NEW record, which is useful for normalization such as maintaining updated_at. Returning NULL from a row-level BEFORE trigger skips that row. An AFTER trigger runs after the row operation has succeeded but before the transaction commits. It cannot alter the stored row, but it is ideal for audit entries because the base-table action has already passed constraints. Constraint triggers add deferrable timing and are appropriate when a rule must be checked at transaction end.

Trigger level is the next choice. FOR EACH ROW fires once for every changed row and can inspect row images. FOR EACH STATEMENT fires once per SQL statement, even when no rows changed. Statement triggers are cheaper for aggregate work, but they do not automatically receive individual row values. PostgreSQL also supports transition tables for some AFTER triggers, allowing statement-level code to read sets of changed rows as relations. That is useful for batch auditing, but row-level audit logs are easier to explain and query, so the examples start there.

Syntax Anatomy

A table trigger has two parts: a trigger function and a trigger binding. The function must return trigger. In PL/pgSQL, it chooses what to do by reading TG_OP and returns NEW, OLD, or NULL as appropriate. The binding names the timing, event, target table, granularity, optional column list, optional WHEN predicate, and function call. Trigger names matter because multiple triggers with the same timing and event fire in name order, so naming is part of behavior.

DROP TABLE IF EXISTS audit_log CASCADE;
DROP TABLE IF EXISTS orders CASCADE;

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_email text NOT NULL,
    status text NOT NULL CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text NOT NULL,
    row_pk text NOT NULL,
    action text NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_by text NOT NULL DEFAULT current_user,
    changed_at timestamptz NOT NULL DEFAULT transaction_timestamp(),
    before_data jsonb,
    after_data jsonb
);

CREATE OR REPLACE FUNCTION audit_orders_row()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, row_pk, action, after_data)
        VALUES (TG_TABLE_NAME, NEW.id::text, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, row_pk, action, before_data, after_data)
        VALUES (TG_TABLE_NAME, NEW.id::text, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, row_pk, action, before_data)
        VALUES (TG_TABLE_NAME, OLD.id::text, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$;

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

This setup creates an orders table, an audit_log table, and one AFTER row trigger. The audit log stores row primary keys as text so one function style can be adapted to tables with different key types. It stores row images as jsonb, which makes the audit table flexible when the source table changes, but it also means consumers must understand JSON fields rather than typed columns.

Example 1: Insert Audit Rows

TRUNCATE orders, audit_log RESTART IDENTITY;

INSERT INTO orders (customer_email, status, total_cents)
VALUES ('sam@example.test', 'new', 2500);

SELECT action, row_pk, after_data->>'status' AS status
FROM audit_log
ORDER BY id;

The insert creates order 1. The trigger then inserts one audit row with action = 'INSERT', no before_data, and an after_data document containing the stored order. The final query returns a deterministic business result: one audit entry for row 1 with status new. The timestamp is intentionally omitted from the verification query because it depends on transaction time.

Example 2: Capture Before and After Values

UPDATE orders
SET status = 'paid', updated_at = now()
WHERE id = 1;

SELECT action,
       before_data->>'status' AS old_status,
       after_data->>'status' AS new_status
FROM audit_log
WHERE action = 'UPDATE';

An update gives the trigger both row images. before_data records the order as PostgreSQL saw it before the update; after_data records the new version. Because PostgreSQL uses MVCC, the old tuple version remains visible to the executing statement long enough for the trigger to serialize it. The expected output proves that the audit row captured the transition from new to paid, not merely the final state.

Example 3: Event-Driven Outbox

CREATE TABLE order_outbox (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_type text NOT NULL,
    aggregate_id bigint NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    processed_at timestamptz
);

CREATE OR REPLACE FUNCTION enqueue_paid_order_event()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status AND NEW.status = 'paid' THEN
        INSERT INTO order_outbox (aggregate_type, aggregate_id, event_type, payload)
        VALUES (
            'order',
            NEW.id,
            'order_paid',
            jsonb_build_object('order_id', NEW.id, 'total_cents', NEW.total_cents)
        );
        PERFORM pg_notify('order_events', NEW.id::text);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER orders_paid_event
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION enqueue_paid_order_event();

NOTIFY is useful for waking listeners, but notifications are small and are delivered only after commit. They are not a durable queue. The outbox pattern solves that by writing an event row in the same transaction as the order update. A worker can later read unprocessed rows, publish to another system, and mark processed_at. The trigger still calls pg_notify, but the notification is only a hint to poll the durable outbox.

TRUNCATE order_outbox RESTART IDENTITY;

UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE id = 1;

SELECT event_type, aggregate_id, payload->>'total_cents' AS cents
FROM order_outbox
ORDER BY id;

The trigger binding uses AFTER UPDATE OF status and a WHEN clause, so changes to other columns do not run the function. Inside the function, IS DISTINCT FROM treats nulls safely and avoids duplicate events when the status is set to the value it already has. The expected result is one order_paid row even though three updates were issued.

Design Choices and Trade-Offs

Use triggers when the rule belongs to the data, not merely to one application workflow. Audit rows, denormalized counters, search-maintenance tables, and transactional outboxes can be good fits because bypassing them would corrupt shared state. Prefer constraints for simple validity rules: a CHECK constraint is clearer and usually faster than a trigger that raises an exception. Prefer application code when the action is non-transactional, slow, or external, such as sending email or calling an HTTP API. A trigger should write a durable request for that work, not perform the network work itself.

Audit detail also has trade-offs. Full jsonb row images are easy to implement and preserve context, but they increase storage and may copy sensitive values. Field-level diffs are smaller and easier to review, but they are more complex and can lose context. Row-level triggers are straightforward but expensive for bulk updates because they execute once per row. Statement-level triggers with transition tables reduce overhead for batch workloads, at the cost of more complex SQL inside the function.

Failure Modes and Troubleshooting

Missing audit rows. Symptom: an order changed but audit_log has no matching row. Cause: the trigger is absent, disabled, attached to the wrong table, or the operation used a path that does not fire that trigger, such as TRUNCATE when only row DML events are configured. Diagnose with SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'orders'::regclass; and inspect the table definition with \dS orders. Correct by recreating or enabling the trigger and backfilling audit records only from trusted recovery data.

Updates fail with a trigger error. Symptom: the application receives an error from the trigger function and the base row is not changed. Cause: trigger work is part of the same statement and transaction. A bug in JSON construction, a missing audit column, or insufficient privilege can abort the original update. Diagnose by reproducing the exact SQL in a transaction, reading the PostgreSQL error context, and checking pg_get_functiondef for the deployed function body. Correct the function in a migration, then rerun the business operation if it is safe and idempotent.

Bulk jobs become slow. Symptom: an update that used to finish quickly now takes minutes and produces heavy WAL. Cause: one audit insert per changed row, plus JSON serialization and indexes on the audit table. Diagnose with EXPLAIN (ANALYZE, BUFFERS) on representative data and compare row counts against audit growth. Correct by narrowing trigger events, adding a WHEN predicate, partitioning or archiving the audit table, or using statement-level transition tables for batch summaries.

Security and Reliability Implications

Audit tables often contain old email addresses, names, prices, or internal notes. Do not grant broad read access just because the table is operational. Put audit tables in a dedicated schema, grant append through the trigger path, and grant review access only to roles that need it. If a trigger function uses SECURITY DEFINER, set a safe search_path and keep the function body small; otherwise an attacker may influence object resolution through objects they control.

Reliability depends on treating triggers as hidden write amplification. They consume locks, CPU, WAL, and disk like any other write. Monitor audit table size, dead tuples, replication lag, and outbox backlog. For event-driven designs, make downstream publishing idempotent: a worker can crash after publishing but before setting processed_at, so event consumers should tolerate repeated event IDs.

Hands-On Lab

Prerequisites: a PostgreSQL database where you can create tables, functions, and triggers, plus a SQL client such as psql. Run the lab in a scratch database, not in a shared production database.

  1. Start a transaction or connect to a disposable database.
  2. Run the setup block to create orders, audit_log, and orders_audit_row.
  3. Run Example 1 and verify one insert audit row appears.
  4. Run Example 2 and verify the audit row shows new changing to paid.
  5. Run the outbox setup and Example 3 verification. Confirm only one order_paid event exists.
  6. Test rollback behavior: begin a transaction, update an order, query the audit table inside the transaction, then roll back and confirm both the order change and audit row disappeared.
  7. Inspect trigger metadata with SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'orders'::regclass;.

Cleanup: remove the lab objects when finished.

DROP TABLE IF EXISTS order_outbox CASCADE;
DROP TABLE IF EXISTS audit_log CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP FUNCTION IF EXISTS enqueue_paid_order_event();
DROP FUNCTION IF EXISTS audit_orders_row();

Assessment Exercises

  1. Change the audit trigger so it ignores updates where only updated_at changed. Which comparison should happen in the trigger, and what output proves it works?
  2. A developer wants the trigger to call a payment webhook directly. Explain why an outbox row plus a worker is safer, and name the duplicate-delivery case the worker must handle.
  3. Design an audit table for a table with personally sensitive columns. Which fields would you omit, mask, or encrypt, and how would that affect investigations?
  4. A nightly job updates 500,000 rows and replication lag spikes. List the trigger-related measurements you would gather before changing the design.
  5. Two triggers fire after the same update. How does PostgreSQL order them, and why can trigger names become a behavior contract?

Summary

PostgreSQL triggers run inside the database executor and inside the surrounding transaction. That makes them powerful for audit trails and event outboxes because they stay atomic with the data change. Choose timing, level, predicates, and row-image storage deliberately; keep external work outside the trigger; and verify behavior with catalog inspection, deterministic queries, rollback tests, and representative bulk workloads.