Triggers in practice

Article.

Every team eventually needs a change log: who touched this invoice, when, and what did it look like before. The application can write it. The question is whether it will — every time, from every code path, including the migration script someone runs at 2am and the psql session during an incident.

That is the case for triggers, and it is a strong one. It is also how teams end up with a database that does things nobody can find in the codebase.

The problem, concretely

You have an invoice table and a compliance requirement: every change to status or amount is recorded with the actor and the previous values.

The obvious implementation

Put it in the service layer:

async def update_invoice(session, invoice_id: UUID, actor: UUID, **changes) -> Invoice:
    invoice = await session.get(Invoice, invoice_id)
    before = {"status": invoice.status, "amount": invoice.amount}

    for field, value in changes.items():
        setattr(invoice, field, value)

    session.add(
        InvoiceAudit(invoice_id=invoice_id, actor_id=actor,
                     before=before, after=changes)
    )
    await session.commit()
    return invoice

This is correct, readable, and testable. It is also one of n places that write to invoice, and it only takes one of the others to break the guarantee:

-- The data fix during an incident. No audit row.
UPDATE invoice SET status = 'paid' WHERE id = '…';

# The backfill script. No audit rows, 40,000 of them.
await session.execute(
    update(Invoice).where(Invoice.status == "pending").values(status="expired")
)

Both are legitimate. Neither goes through your service. The compliance requirement said every change, and you now have a log that is silently incomplete — which is worse than no log, because people trust it.

What a trigger actually is

The two objects

A trigger is a function the database calls automatically, inside the same transaction as the statement that fired it. Two objects: the function, and the binding.

CREATE FUNCTION audit_invoice_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status
       OR NEW.amount IS DISTINCT FROM OLD.amount THEN
        INSERT INTO invoice_audit (invoice_id, actor_id, before, after, changed_at)
        VALUES (
            OLD.id,
            current_setting('app.actor_id', true)::uuid,
            jsonb_build_object('status', OLD.status, 'amount', OLD.amount),
            jsonb_build_object('status', NEW.status, 'amount', NEW.amount),
            now()
        );
    END IF;
    RETURN NEW;
END $$;

CREATE TRIGGER trg_audit_invoice
    AFTER UPDATE ON invoice
    FOR EACH ROW
    EXECUTE FUNCTION audit_invoice_change();

Four details in there do real work, and each is a place people get it wrong.

The four details that matter

IS DISTINCT FROM, not <>. In SQL, NULL <> NULL is NULL, which is not true, so the IF does not fire. An invoice going from amount = NULL to amount = 100 would produce no audit row with <>. IS DISTINCT FROM treats two nulls as equal and a null against a value as different — which is the comparison you meant.

AFTER, not BEFORE. A BEFORE trigger runs before the row is written and can modify NEW or veto the write by returning NULL. An AFTER trigger sees the row as it will be. For auditing you want AFTER: if the write is rejected by a constraint after your BEFORE trigger logged it, you have logged a change that never happened.

current_setting('app.actor_id', true). The database does not know who your user is — it knows the connection’s role, which is one service account for every request. The actor has to be passed in, and a transaction-local setting is how. That second argument, true, means “return NULL if unset” rather than raising; without it, any write from a path that did not set the variable fails outright.

async with session.begin():
    await session.execute(
        text("SELECT set_config('app.actor_id', :actor, true)"),
        {"actor": str(actor_id)},
    )
    ...  # every write in this transaction now carries the actor

The true on set_config is the transaction-local flag, and it matters more than it looks: with a connection pool, a session-level setting outlives the request and the next request on that connection inherits the previous user’s identity. That is an audit log that attributes changes to the wrong person, which is worse than one that attributes them to nobody.

Row or statement

FOR EACH ROW. The alternative, FOR EACH STATEMENT, fires once per statement regardless of how many rows changed — useful for “something changed in this table” notifications, useless for per-row auditing because OLD and NEW are not available.

Where it bites

The bulk update

The trigger above is fine at one row per request. Now run the backfill:

UPDATE invoice SET status = 'expired' WHERE status = 'pending';
-- UPDATE 40000

That is one statement, and 40,000 PL/pgSQL function invocations, 40,000 INSERT statements, and 40,000 new rows in invoice_audit — all inside one transaction, which holds its locks until it commits and cannot be vacuumed until it ends. A migration that would have taken a second takes minutes, and the audit table has grown by more than the table being audited.

There is no clever fix. The options are all trade-offs:

• Disable the trigger for the migration, and write the audit rows yourself in one INSERT ... SELECT. Requires ALTER TABLE ... DISABLE TRIGGER, which takes an ACCESS EXCLUSIVE lock — brief, but it blocks everything.
• Batch the update into chunks with commits between, so no single transaction is enormous. Slower in total, survivable in production.
• Accept it and run the migration in a maintenance window.

The mistake is not knowing this until the migration is running.

The invisible cascade

CREATE TRIGGER trg_recalc_total AFTER INSERT OR UPDATE ON order_line
    FOR EACH ROW EXECUTE FUNCTION recalc_order_total();

recalc_order_total() updates orders. If orders has its own audit trigger, one INSERT into order_line fires two triggers and writes three rows. Add a third trigger on the audit table — someone will — and you have a chain nobody has drawn a diagram of.

Postgres will stop genuinely infinite recursion by exhausting the stack, but the common case is not infinite. It is a cascade four deep that quintuples your write amplification and shows up as “the database got slow” with no query to blame, because the expensive work is not in any statement your application sent.

Find them before you need to:

SELECT c.relname AS table_name, t.tgname AS trigger_name, p.proname AS function_name
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_proc p ON p.oid = t.tgfoid
WHERE NOT t.tgisinternal
ORDER BY 1, 2;

Run that on any unfamiliar production database before you believe you understand what a write does. Constraint triggers backing foreign keys are excluded by tgisinternal; what is left is what humans added.

The replica that disagrees

Logical replication ships row changes, not statements, so triggers do not fire on the subscriber by default. A trigger that maintains a denormalised counter keeps it correct on the primary and lets it drift on every replica, until someone reads the counter from a replica and gets a different number.

ALTER TABLE … ENABLE REPLICA TRIGGER opts a trigger in. Knowing that the default is off is the part that matters.

When a trigger is the right answer

The test is not “is this data-related”. It is: can this invariant be violated by a writer I do not control?

Audit is the strongest case, because the requirement is literally “no path may skip this” and the application layer cannot promise that. updated_at is the same argument in miniature.

The strongest case against is anything that makes a decision. A trigger that refunds a customer, sends an email, or calls an API is unreviewable, untestable without a database, and invisible in the stack trace of the request that caused it. It will also happily run inside a transaction that later rolls back — the database write disappears, the email does not.

What you owe the next reader

If you add a trigger, the codebase must admit it exists. Three habits:

1. The migration is the documentation. The trigger’s definition lives in version control as a migration, never applied by hand in psql.
2. A comment where the effect is visible. On the model or the repository, pointing at the trigger, because someone reading Invoice needs to know writes have side effects.
3. A test that proves it fires. Update a row through the ORM, assert the audit row exists. Without it the trigger is one careless migration away from being silently dropped.

async def test_status_change_writes_audit(session, invoice):
    await session.execute(
        text("SELECT set_config('app.actor_id', :a, true)"), {"a": str(ACTOR)}
    )
    invoice.status = "paid"
    await session.commit()

    result = await session.execute(
        select(InvoiceAudit).where(InvoiceAudit.invoice_id == invoice.id)
    )
    rows = result.scalars().all()
    assert len(rows) == 1
    assert rows[0].before["status"] == "pending"
    assert rows[0].actor_id == ACTOR

That test is what stops a trigger from becoming folklore.

Related

• Functions and procedures - the function a trigger calls
• Views and materialized views - the other server-side objects
• Zero-downtime migrations - shipping the trigger safely
• Replication and Logical Decoding - why triggers are off on subscribers