What is a trigger, and what are common use cases and pitfalls?

Anatomy of a trigger

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, now());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

• BEFORE vs AFTER: a BEFORE trigger can inspect and modify the row before it's written (e.g., auto-populating updated_at), and can even prevent the write entirely by raising an exception; an AFTER trigger runs once the change is already committed to the row and is typically used for side effects (logging, cascading updates) rather than modifying the row itself.
• FOR EACH ROW vs statement-level: a row-level trigger fires once per affected row; a statement-level trigger fires once total, regardless of how many rows a single statement touched.
• OLD and NEW: special row references available inside the trigger — OLD is the row's prior state (available for UPDATE/DELETE), NEW is the row's new state (available for INSERT/UPDATE).

Legitimate use cases

• Audit logging — recording who changed what and when, guaranteed to fire regardless of which application or tool made the change.
• Enforcing invariants too complex for a CHECK constraint — e.g., "the sum of allocations across related rows can never exceed a parent's capacity," which spans multiple rows/tables.
• Maintaining denormalized/derived columns — keeping a cached order_count on a customers row in sync whenever orders changes, without every application code path having to remember to update it manually.
• Enforcing referential integrity rules more complex than a standard foreign key can express (e.g., conditional referential integrity).

Common pitfalls

• Hidden "action at a distance." A developer looking at UPDATE orders SET status = 'shipped' has no way to know, just from reading that line, that it also silently fires three triggers that update two other tables — this makes systems significantly harder to reason about and debug, especially for someone new to the codebase.
• Performance overhead on high-throughput writes. Every trigger adds work to every matching INSERT/UPDATE/DELETE; a row-level trigger on a table with heavy write volume can become a meaningful bottleneck, especially if the trigger itself does further queries/writes.
• Cascading and recursive triggers. A trigger on table A that writes to table B, which has its own trigger that writes back to table A, can create confusing chains of execution (or, if unguarded, infinite loops) that are very difficult to trace.
• Silent failure surprises. If a BEFORE trigger raises an exception, the original statement fails too — this is usually desired (it enforces the invariant), but if the trigger's logic has a bug, it can cause completely unrelated-looking application writes to fail with a confusing error that doesn't obviously point back to the trigger.

Triggers are the right tool when an invariant or side effect must be enforced regardless of caller and cannot be expressed as a simpler constraint. For anything that could reasonably live in explicit application code instead (most business logic), prefer application code — it's visible, testable, and versioned alongside the rest of the system, rather than hidden in the schema.