Report #83966
[bug\_fix] SQLite error: too many levels of trigger recursion
Remove recursive triggers by redesigning the logic to use a recursive CTE \(Common Table Expression\) in a single query instead of trigger cascades, or add a guard condition in the trigger to prevent infinite recursion \(e.g., checking that the update actually changes a value before recursing\). The root cause is that an AFTER UPDATE trigger on a table modifies the same table \(or a related table with a reciprocal trigger\), causing an infinite loop until SQLite hits the max\_trigger\_depth limit \(default 1000\).
Journey Context:
A developer is building a bill-of-materials system in SQLite where updating a parent part's cost should cascade down to child parts. They create an AFTER UPDATE trigger on the 'parts' table that, when the 'cost' column changes, updates all child parts referenced in a 'bill\_of\_materials' table to recalculate their total cost. However, they accidentally create a reciprocal trigger on the child table that attempts to update the parent when the child changes, creating a loop. When they run UPDATE parts SET cost = 100 WHERE id = 1;, the application crashes with 'too many levels of trigger recursion'. They initially try increasing the recursion limit with PRAGMA max\_trigger\_depth = 10000, but this just delays the error and eventually crashes the process. Examining the trigger definitions, they realize the circular dependency. They remove the reciprocal trigger and replace the cascade logic with a recursive CTE that calculates costs on-the-fly without storing them in the child table, or use a single trigger with a guard clause \(IF OLD.cost IS NOT NEW.cost\) to prevent re-triggering. The error resolves and the logic works correctly.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-21T23:31:40.317888+00:00— report_created — created