Trigger-Based Audit Logging

You will implement a comprehensive audit trail system using transition tables and trigger functions.

In this lesson, you will learn how to create a permanent, automated record of every change made to your data using PostgreSQL trigger functions and transition tables.

The Concept

When dealing with sensitive data, knowing what changed and who changed it is critical. While logical decoding (which we touched on in module 12) tracks changes at the WAL level, trigger-based auditing allows us to capture changes within the application's transactional context.

By using AFTER triggers, we can inspect exactly what was inserted, updated, or deleted. The power of modern PostgreSQL lies in Transition Tables (REFERENCING NEW TABLE AS ...), which allow a trigger to see the entire set of affected rows at once rather than processing them row-by-row.

Implementation

Suppose we want to audit a employees table. First, we create an audit_log table, then we write a trigger function that captures the state of the data.

CREATE TABLE employee_audit (
    operation TEXT,
    changed_at TIMESTAMP DEFAULT now(),
    old_data JSONB,
    new_data JSONB
);

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (operation, old_data, new_data)
    SELECT TG_OP, to_jsonb(o), to_jsonb(n)
    FROM old_table o FULL OUTER JOIN new_table n ON o.id = n.id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_employee
AFTER INSERT OR UPDATE OR DELETE ON employees
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION audit_trigger_func();

Why this matters

Using REFERENCING NEW TABLE is significantly more efficient than row-level triggers because it performs a single set-based operation rather than executing the function for every single row involved in a bulk UPDATE. The audit log acts as a "source of truth" that is separate from your main business logic, ensuring that even if an application bug causes an accidental deletion, you have an immutable trail of what was lost. This approach effectively bridges the gap between your procedural logic and the underlying storage state.

Key Takeaway: Trigger-based auditing using transition tables provides a high-performance, set-based mechanism to capture historical data changes without sacrificing the integrity of your primary transactions.