Triggers Not Recursing in Hierarchical Data Deletion: Causes & Fixes

Understanding Trigger-Based Cascading Deletes in Hierarchical Structures

Issue Overview: Non-Recursive Trigger Behavior During Hierarchical Data Deletion

The core issue revolves around a hierarchical data model implemented in SQLite where a trigger is designed to perform cascading deletions. The table objects stores hierarchical relationships using parent and cntr_id columns, with item_id representing a unique identifier for each item. A trigger named objects_parents_rem is created to automatically delete child records when a parent is deleted, mimicking the behavior of ON DELETE CASCADE in foreign key constraints. However, when executing a series of DELETE statements targeting orphaned records (nodes without valid parents), the trigger does not recursively process all descendants in a single pass. Instead, multiple manual deletions are required to fully prune the tree, indicating that the trigger fires only once per direct deletion event and does not propagate through the hierarchy as expected.

The trigger’s logic is structured to delete child records when a parent is removed, provided two conditions are met:

1. The deleted parent has child nodes in the hierarchy (EXISTS(SELECT 1 FROM objects WHERE (parent, cntr_id)=(OLD.item_id, OLD.cntr_id))).
2. No other references to the deleted parent’s item_id and cntr_id exist in the table (NOT EXISTS(SELECT 1 FROM objects WHERE (item_id, cntr_id)=(OLD.item_id, OLD.cntr_id))).

This design assumes that deleting a parent node will trigger the deletion of its immediate children, and those deletions will, in turn, trigger the same logic for their children, recursively. However, the observed behavior shows that after the first DELETE operation, orphaned child nodes remain in the table. Subsequent DELETE statements continue to reduce the row count, proving that the trigger is not propagating deletions beyond the first level of the hierarchy. This inconsistency suggests a failure in recursive trigger execution.

Root Causes: Why the Trigger Fails to Propagate Deletions Recursively

The primary cause of this behavior lies in SQLite’s default configuration regarding recursive triggers. SQLite disables recursive trigger execution by default for compatibility reasons. A trigger will not activate other triggers (including itself) unless the recursive_triggers pragma is explicitly enabled. This setting is critical for hierarchical deletion workflows, as each DELETE operation performed by the trigger must itself invoke the same logic to process child nodes at deeper levels of the hierarchy. Without recursion, the trigger only processes direct children of the initially deleted nodes, leaving grandchildren and deeper descendants intact.

Secondary factors contributing to the issue include:

1. Foreign Key Constraints Not Utilized: The table’s commented-out foreign key definition (-- FOREIGN KEY (parent, cntr_id) REFERENCES objects(item_id, cntr_id) ON DELETE CASCADE ON UPDATE CASCADE) hints at an alternative approach that was not fully implemented. If enabled, this constraint would automate cascading deletions natively, bypassing the need for a custom trigger. However, foreign key support in SQLite requires explicit activation via PRAGMA foreign_keys = ON;, which may not have been applied.
2. Trigger Condition Over-Constraint: The WHEN clause’s second condition (NOT EXISTS(...)) ensures that the trigger only fires if the deleted parent has no other occurrences in the table. This creates a dependency on the uniqueness of item_id and cntr_id, which may not align with the data model’s requirements. If multiple nodes reference the same item_id and cntr_id (e.g., in a graph structure), this condition could prevent the trigger from firing even when valid children exist.
3. Indexing and Performance Overheads: With millions of rows, the efficiency of the EXISTS subqueries in the trigger’s WHEN clause becomes critical. Missing or suboptimal indexes on parent, cntr_id, or item_id could lead to full table scans during trigger execution, causing timeouts or incomplete deletions in large datasets.

Resolving the Issue: Enabling Recursive Triggers and Optimizing the Data Model

Step 1: Enable Recursive Triggers in SQLite

To allow triggers to activate recursively, enable the recursive_triggers pragma at the start of the session or transaction:

PRAGMA recursive_triggers = ON;

This setting must be applied before executing any DELETE statements. Without this, the trigger will only process the immediate children of the initially deleted rows, leaving deeper descendants orphaned.

Step 2: Simplify the Workflow with Native Foreign Key Cascades

If the data model permits, replace the custom trigger with SQLite’s built-in foreign key cascading behavior. Uncomment and activate the foreign key constraint:

CREATE TABLE objects (
    cntr_id INTEGER NOT NULL,
    parent INTEGER NOT NULL,
    item_id INTEGER NOT NULL, 
    FOREIGN KEY (parent, cntr_id) REFERENCES objects(item_id, cntr_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (name) REFERENCES names(id) ON DELETE RESTRICT
) STRICT;

Ensure foreign keys are enabled for the database connection:

PRAGMA foreign_keys = ON;

This approach delegates cascading deletions to the database engine, eliminating the need for custom triggers. However, this requires that (item_id, cntr_id) be a UNIQUE or PRIMARY KEY combination, as foreign keys cannot reference non-unique columns.

Step 3: Validate Trigger Conditions and Indexing

If retaining the custom trigger is necessary, revise the WHEN clause to remove the NOT EXISTS condition unless strictly required by the business logic. For example:

CREATE TRIGGER objects_parents_rem AFTER DELETE ON objects
WHEN EXISTS(SELECT 1 FROM objects WHERE (parent, cntr_id)=(OLD.item_id, OLD.cntr_id))
BEGIN
    DELETE FROM objects WHERE (parent, cntr_id)=(OLD.item_id, OLD.cntr_id);
END;

This ensures the trigger fires for any deleted parent with children, regardless of other references to item_id and cntr_id.

Additionally, verify that indexes support efficient lookups on parent, cntr_id, and item_id. The existing indexes parent_descendent and cont_itm should suffice, but benchmark their performance with EXPLAIN QUERY PLAN on critical DELETE operations.

Step 4: Batch Deletion with Recursive CTEs (Optional)

For scenarios requiring manual cleanup of orphaned hierarchies, use a recursive Common Table Expression (CTE) to identify all descendants in a single query:

WITH RECURSIVE orphaned_nodes AS (
    SELECT item_id, cntr_id FROM objects 
    WHERE NOT EXISTS (
        SELECT 1 FROM objects o 
        WHERE o.item_id = objects.parent AND o.cntr_id = objects.cntr_id
    )
    UNION ALL
    SELECT o.item_id, o.cntr_id FROM objects o
    INNER JOIN orphaned_nodes ON o.parent = orphaned_nodes.item_id AND o.cntr_id = orphaned_nodes.cntr_id
)
DELETE FROM objects WHERE (item_id, cntr_id) IN (SELECT item_id, cntr_id FROM orphaned_nodes);

This method bypasses trigger recursion entirely by precomputing the full set of orphaned nodes, ensuring a single-pass deletion.

Step 5: Monitor Trigger Depth and Limitations

SQLite imposes a limit on trigger recursion depth, configurable via PRAGMA trigger_depth_limit. For extremely deep hierarchies, adjust this setting to prevent truncation:

PRAGMA trigger_depth_limit = 1000;  -- Adjust based on expected hierarchy depth

By systematically addressing recursive trigger configuration, leveraging native foreign key behaviors, and optimizing data access patterns, the hierarchical deletion workflow can achieve atomic, efficient, and reliable operation across all levels of the tree structure.