A trigger on table A writes a row into table B, and a trigger on table B writes back into table A. Walk through what happens when an application inserts one row into A, and explain how relational engines limit cascading or recursive trigger firing and how you would design around it.
answer
- trigger DML fires more triggers
- direct vs mutual (A to B to A) recursion
- depth cap, not cycle detection
- one transaction: all rolls back
- guard on actual change / idempotent write
basics
~20 sEach trigger's own DML fires more triggers, so A to B to A can loop. Engines stop it with a nesting-depth cap or by disabling self-recursion by default, and the loop aborts the whole transaction. Fix it with change guards, idempotent writes, or by moving the second hop into application code.
solid answer
~50 sThe insert into A fires A's trigger, whose insert into B fires B's trigger, whose insert back into A fires A's trigger again — a cycle. Nothing detects the cycle up front; it just recurses until an engine limit trips. The guards differ. SQL Server caps nesting at 32 levels and disables *direct* recursion by default (a trigger re-firing itself) while indirect A-to-B-to-A recursion still runs if nested triggers are on. PostgreSQL has no cycle detector — recursion runs until the stack-depth limit errors out. Oracle raises a maximum-recursion error, and a row-level trigger that queries its own table hits the mutating-table error instead. When the limit trips, the error propagates out of the original statement and rolls back everything, including writes that looked successful. Design fixes: write only when the value actually changed, guard with a session flag or sentinel column, make writes idempotent so a second pass is a no-op, or take the second hop out of the database into the application or an outbox consumer.
code
sql · 11 linesCREATE FUNCTION sync_status_to_b() RETURNS trigger AS $$
BEGIN
IF NEW.status IS NOT DISTINCT FROM OLD.status THEN
RETURN NULL; -- nothing relevant changed, do not propagate
END IF;
UPDATE b SET status = NEW.status
WHERE a_id = NEW.id
AND status IS DISTINCT FROM NEW.status; -- idempotent: no-op on second pass
RETURN NULL;
END;
$$ LANGUAGE plpgsql;go deeper
Know that DML inside a trigger fires other triggers, so triggers can chain and even loop.
Trace the A to B to A chain explicitly, and know it ends with an engine depth or stack error that rolls the transaction back.
Talk about diagnosis and mitigation: change guards, idempotent writes, write amplification, lock-hold time, and the near-limit cascade that passes tests.
Frame it as graph design — the trigger dependency graph must be acyclic and shallow, and cross-table propagation belongs in an explicit outbox or service step.
## What cascading means Trigger bodies contain DML. That DML is not special: it is subject to triggers exactly like application DML. So a trigger's INSERT into another table fires that table's triggers, whose statements fire more triggers, and so on. The chain is called *cascading* firing; when it returns to a table already in the chain it is *recursive* firing. Two flavors matter: - **Direct recursion**: a trigger on A performs DML on A, re-firing itself. - **Indirect (mutual) recursion**: A fires B fires A. This is the case in the question, and the one people build by accident, because each trigger looks innocent in isolation. ## What actually happens at runtime The application issues one INSERT into A. Depth 1: A's AFTER trigger inserts into B. Depth 2: B's trigger inserts into A. Depth 3: A's trigger inserts into B again. There is no fixpoint unless something in the data or the logic breaks the cycle. Execution is depth-first and synchronous, all inside the original transaction, so every intermediate row is written and every row lock is held. Termination comes from an engine limit rather than from cleverness: - **SQL Server** caps trigger/procedure nesting at 32 levels. It also disables direct recursion by default via a database option, but that option does not stop the A-to-B-to-A form; only turning nested triggers off entirely does. - **PostgreSQL** has no trigger-recursion detector at all. The recursion consumes stack until the configured stack-depth limit raises an error. - **Oracle** raises a maximum recursion-level error; separately, a row-level trigger that reads or writes the table it fires on hits the mutating-table error, which prevents some — not all — self-recursive shapes. - **MySQL** simply forbids a trigger from performing DML on the table it is defined on, and caps nesting. When the limit trips, the error is raised inside the original statement. Because triggers run in the invoking transaction, the whole transaction rolls back: none of the intermediate rows survive. That is the merciful outcome. The dangerous outcome is a cycle that terminates *just* under the limit for typical data — for example an update trigger that only re-fires when a value changes — because it works in test and produces enormous write amplification in production. ## Why this is hard to diagnose The stack trace, if you get one, names trigger functions, not application code. The application only sees one failed INSERT. Depth is data-dependent, so it reproduces only with certain rows. And each hop multiplies work: an update touching 1,000 rows that cascades three levels with a one-to-many fan-out can turn into hundreds of thousands of row writes, all in one transaction holding locks the entire time. ## Designing around it **Break the cycle in the data flow, not with the depth limit.** Concretely: 1. **Fire only on real change.** For UPDATE triggers, compare old and new values and exit early when nothing relevant changed. This turns an infinite mutual update into a two-hop, self-terminating one. 2. **Make the write idempotent.** If B's trigger writes a value into A that A already has, a change guard plus idempotent write means the second pass is a no-op. 3. **Use an explicit guard.** A session-scoped flag or a sentinel column set by the first hop lets later hops detect "I am already inside this cascade" and skip. It works, but it is state the next reader will not expect — document it. 4. **Make the graph acyclic by design.** Draw the table-to-table trigger graph. If it has a cycle, that is a design defect to remove, usually by deciding which table is the source of truth and letting only one direction of propagation exist. 5. **Move the second hop out.** Have the trigger write an event row (an outbox) and let an application consumer do the downstream write. The cascade becomes an explicit, observable, retryable step instead of hidden recursion. ## The interview signal Weak answers say "the database detects the loop and stops it." Strong answers say the engine only imposes a blunt depth cap, the failure surfaces as an unrelated-looking error on the original statement, everything rolls back, and the real fix is to remove the cycle from the trigger graph rather than to raise a limit.
- Raising the nesting limit is sometimes suggested as a fix. Why is that usually wrong?The limit is a safety net, not a business rule. If a legitimate workload needs deeper nesting, the trigger graph is doing work that should be explicit; if the workload is a genuine cycle, raising the limit just makes the failure slower, larger and more expensive to roll back. Raise it only when you have proven a bounded, intentional depth.
- How would you find an accidental cascade in an existing database?Enumerate triggers from the catalog and build a directed graph of which table each trigger writes to, then look for cycles. Complement that with runtime evidence: statement timing orders of magnitude above the row count, transaction-log volume far exceeding the rows the application touched, and long lock-hold times on tables the application never named.
saying these in an interview costs you the question
- Saying the database detects trigger cycles and refuses to create them
- Believing intermediate rows written before the depth error survive the failure
- Assuming disabling recursive triggers in SQL Server also stops A-to-B-to-A cascades
- Treating the nesting limit as the fix rather than as a symptom detector
- Claiming cascading triggers run asynchronously outside the transaction