Case Study 2 — The Invisible Trigger

A trigger is logic that runs without anyone calling it. That's its power and its peril. A debugging session that should have taken minutes took two days because the cause was a trigger nobody knew existed.

Background

A developer was investigating two complaints about an orders table. First, inserting an order was unexpectedly slow — far slower than the simple INSERT suggested. Second, rows kept appearing in a notifications table that the developer's code never wrote to. They read their application code top to bottom, found nothing that touched notifications, and could not explain the slowness. They suspected a bug in the ORM, the connection pool, even the network.

The actual cause was a trigger added months earlier by a different team:

CREATE TRIGGER orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION notify_and_enrich();

The trigger function notify_and_enrich() did three things on every inserted order: inserted a row into notifications, called an expensive function to compute a "risk score," and updated a denormalized counter on the customers table. None of it was visible in the application code — it ran automatically, inside the database, on every insert.

Why it was so hard to find

The developer's mental model was "my code is the only thing that touches this data." Triggers violate that assumption: they run invisibly, attached to the table, not to any call site. Nothing in the INSERT statement or the application referenced notifications or the risk score — yet both happened. There was no stack trace pointing at the trigger, no log line saying "trigger fired." The behavior was real but sourceless from the application's point of view.

The slowness had the same root: the "expensive risk score" computation ran per inserted row, synchronously, as part of the insert transaction. A bulk import of 10,000 orders fired the trigger 10,000 times, each doing heavy work — turning a fast bulk insert into a crawl.

How it was finally found

The developer eventually ran \d orders in psql and saw, at the bottom, a "Triggers:" section listing orders_after_insert. Inspecting the trigger function revealed everything. The whole mystery collapsed in five minutes once they looked at the table's definition rather than the application's code.

The resolution

The team didn't remove the trigger outright (the notifications and counter were genuinely needed), but they fixed the design:

  1. Documented it. The trigger and its effects were written into the table's documentation and the team wiki, so the next person isn't ambushed.
  2. Made it lean. The expensive risk-score computation was moved out of the synchronous trigger into an asynchronous job (the trigger now just enqueues work). Triggers must be fast, because they run inside every affected transaction.
  3. Reconsidered what belongs in a trigger. The denormalized counter (a derived value) was a reasonable trigger use; the notification side-effect was moved to an explicit, visible application step where future developers would actually see it. Cross-system side-effects hidden in triggers are a known anti-pattern.
  4. Preferred constraints where possible. A separate rule that had been enforced by a trigger (total >= 0) was replaced with a plain CHECK constraint — simpler, faster, and visible in the schema.

The analysis

  1. Triggers run invisibly. They're attached to a table and fire automatically; nothing at the call site reveals them. This makes them powerful (universal enforcement, automatic auditing) and dangerous (surprising behavior, untraceable side-effects). Always check \d <table> for triggers when behavior is mysterious.

  2. Triggers run inside the transaction, per row. Heavy work in a per-row trigger multiplies across every affected row and blocks the writing transaction. Keep trigger logic lean; push expensive or external work to asynchronous jobs.

  3. Hidden side-effects are an anti-pattern. Auditing and maintaining derived columns are good trigger uses (they're invisible by design and that's fine). But cross-system side-effects (sending notifications, calling services) hidden in triggers surprise everyone — make those explicit in application code where they're visible.

  4. Prefer constraints for rules constraints can express. A CHECK or foreign key is declarative, fast, and visible in the schema; a trigger reimplementing the same rule is slower and hidden. Reserve triggers for what constraints genuinely can't do.

  5. Document the invisible. The single cheapest fix was documentation. Any automatic, invisible behavior — triggers, scheduled refreshes, background jobs — must be written down, or it becomes a multi-day mystery for the next person.

Discussion questions

  1. Why couldn't the developer find the cause by reading application code? What assumption failed?
  2. Why was the insert slow, specifically? How does "per row, inside the transaction" explain it?
  3. Which of the trigger's three actions is a reasonable trigger use, and which should be explicit application logic? Why?
  4. When should a rule be a CHECK/FK constraint instead of a trigger?
  5. ⭐ Propose a team norm for triggers (and other invisible automation) that prevents this class of multi-day debugging mystery.