Triggers — Automating Audit Trails and Derived Data

A trigger is a function that executes automatically in response to a row-level or statement-level event on a table. Triggers ensure that business rules are enforced at the database level, independent of which application or API writes to the table.

Trigger types

Example 1: Audit log trigger

-- Audit log table
CREATE TABLE IF NOT EXISTS audit_trail (
    audit_id    BIGSERIAL PRIMARY KEY,
    table_name  TEXT NOT NULL,
    record_id   TEXT NOT NULL,
    operation   TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
    old_data    JSONB,
    new_data    JSONB,
    changed_at  TIMESTAMPTZ DEFAULT NOW(),
    changed_by  TEXT DEFAULT CURRENT_USER
);

-- Generic audit trigger function (works for any table)
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_trail (table_name, record_id, operation, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        CASE TG_OP
            WHEN 'DELETE' THEN (row_to_json(OLD) ->> 'invoice_id')
            ELSE (row_to_json(NEW) ->> 'invoice_id')
        END,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::JSONB ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::JSONB ELSE NULL END
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to the invoices table
CREATE TRIGGER audit_invoices
    AFTER INSERT OR UPDATE OR DELETE ON invoices
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Test it:
UPDATE invoices SET status = 'paid' WHERE invoice_id = 1;
SELECT * FROM audit_trail WHERE table_name = 'invoices' ORDER BY audit_id DESC LIMIT 3;

Example 2: BEFORE trigger for data validation and enrichment

-- Automatically set invoice_no if not provided, and validate due_date
DROP TRIGGER IF EXISTS before_invoice ON invoices;
CREATE OR REPLACE FUNCTION before_invoice_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- Auto-generate invoice_no if not provided
    IF NEW.invoice_no IS NULL THEN
        NEW.invoice_no := 'INV-' || LPAD(nextval('invoice_seq')::TEXT, 6, '0');
    END IF;

    -- Due date must be after issue date
    IF NEW.due_date <= NEW.issue_date THEN
        RAISE EXCEPTION 'due_date (%) must be after issue_date (%)',
            NEW.due_date, NEW.issue_date;
    END IF;

    -- Default to 'sent' status
    IF NEW.status IS NULL THEN
        NEW.status := 'sent';
    END IF;

    RETURN NEW;  -- BEFORE triggers must return NEW (or NULL to abort)
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_invoice
    BEFORE INSERT ON invoices
    FOR EACH ROW EXECUTE FUNCTION before_invoice_insert();

Example 3: Maintaining denormalised running totals

-- Automatically keep a client_stats table in sync with invoice changes
CREATE TABLE client_stats (
    client_id       INT PRIMARY KEY REFERENCES clients,
    invoice_count   INT DEFAULT 0,
    total_billed    NUMERIC(12,2) DEFAULT 0,
    total_collected NUMERIC(12,2) DEFAULT 0,
    last_invoice_at DATE
);

CREATE OR REPLACE FUNCTION sync_client_stats()
RETURNS TRIGGER AS $$
BEGIN
    -- Upsert the stats row for this client
    INSERT INTO client_stats (client_id, invoice_count, total_billed, last_invoice_at)
    SELECT
        i.client_id,
        COUNT(*),
        COALESCE(SUM(ii.quantity * ii.unit_price), 0),
        MAX(i.issue_date)
    FROM invoices i
    LEFT JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    WHERE i.client_id = COALESCE(NEW.client_id, OLD.client_id)
    GROUP BY i.client_id
    ON CONFLICT (client_id) DO UPDATE SET
        invoice_count   = EXCLUDED.invoice_count,
        total_billed    = EXCLUDED.total_billed,
        last_invoice_at = EXCLUDED.last_invoice_at;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_stats_on_invoice
    AFTER INSERT OR UPDATE OR DELETE ON invoices
    FOR EACH ROW EXECUTE FUNCTION sync_client_stats();

Key Takeaway

Triggers enforce database-level invariants: audit logs that no application can bypass, auto-generated fields, and denormalised caches that stay in sync automatically. BEFORE triggers can modify NEW before it's written; AFTER triggers see the committed state. Use row_to_json(OLD)::JSONB and row_to_json(NEW)::JSONB in audit triggers to capture the full row state without listing every column. RAISE EXCEPTION aborts the transaction with a clear error message.