也许可以考虑使用以下组合：
1.使用ON DELETE CASCADE自动删除子项的外键，以及
1.用于收集删除ID的表，以及

1. 3个AFTER DELETE触发器自动将插入填充到集合表中。
   这将允许被删除的id的，以获得作为和当想要的
   下面是一个demo：

/* just in case cleanup */
DROP TABLE IF EXISTS leaf_nodes;
DROP TABLE IF EXISTS inner_nodes;
DROP TABLE IF EXISTS root_nodes;

/* Make sure that Foreign Key handling is enabled */
PRAGMA foreign_keys = on;

/* The original tables */
CREATE TABLE IF NOT EXISTS "root_nodes" (
    "id"    INTEGER NOT NULL,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "inner_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL /*>>>>>>>>>> ADDED >>>>>>>>>>*/ REFERENCES root_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "leaf_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL /*>>>>>>>>>> ADDED >>>>>>>>>>*/ REFERENCES inner_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- other stuff
    PRIMARY KEY("id")
);
/*<<<<<<<<<< NEW TABLE FOR THE COLLECTION OF DELETED IDs >>>>>>>>>>*/
CREATE TABLE IF NOT EXISTS deletion_log (timestamp INTEGER DEFAULT (strftime('%s','now')), type TEXT, deletedId INTEGER); 
/*<<<<<<<<< 3 AFTER DELETE TRIGGERS >>>>>>>>>>*/
CREATE TRIGGER IF NOT EXISTS leaf_nodes_delete_trigger AFTER DELETE ON leaf_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('3LN',old.id);
    END
;
CREATE TRIGGER IF NOT EXISTS inner_nodes_delete_trigger AFTER DELETE ON inner_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('2IN',old.id);
    END
;
CREATE TRIGGER IF NOT EXISTS root_nodes_delete_trigger AFTER DELETE ON root_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('1RN',old.id);
    END
;
/* DEMONSTRATE THE ABOVE */
/* 1 Load some root_nodes */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 10)
INSERT OR IGNORE INTO root_nodes SELECT * FROM cte_count;

/* 2 Load some inner_nodes (approx 10 per root) */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 100)
INSERT OR IGNORE INTO inner_nodes SELECT *,(abs(random() % 10)) + 1 FROM cte_count;
/* 3 Load some leaf_nodes again approx 10 fir inner node */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 1000)
INSERT OR IGNORE INTO leaf_nodes SELECT *,(abs(random() % 100)) + 1 FROM cte_count;

/* Show the data after the initial load */
SELECT * FROM deletion_log ORDER BY type ASC; /*<<<<<<<<<< RESULT 1*/
SELECT * FROM root_nodes 
    JOIN inner_nodes ON inner_nodes.parent = root_nodes.id
    JOIN leaf_nodes ON leaf_nodes.parent = inner_nodes.id
ORDER BY root_nodes.id,inner_nodes.id,leaf_nodes.id /*<<<<<<<<<< RESULT 2*/
;
/*!!!!!!!!!! DELETE SOME ROOT NODES !!!!!!!!!!*/
DELETE FROM root_nodes WHERE (id % 2) = 1;
/* Show the data after the deletions */
SELECT * FROM deletion_log ORDER BY type ASC; /*<<<<<<<<<< RESULT 3*/
SELECT * FROM root_nodes 
    JOIN inner_nodes ON inner_nodes.parent = root_nodes.id
    JOIN leaf_nodes ON leaf_nodes.parent = inner_nodes.id
ORDER BY root_nodes.id,inner_nodes.id,leaf_nodes.id /*<<<<<<<<<< RESULT 4 */
;

/* Cleanup after demo */
DROP TABLE IF EXISTS leaf_nodes;
DROP TABLE IF EXISTS inner_nodes;
DROP TABLE IF EXISTS root_nodes;
DROP TABLE IF EXISTS deletion_log;

字符串

运行结果

• 请注意，由于数据的随机生成，每次运行的结果都会有所不同
• 结果1*

的数据

• 正如预期的那样，delete_log表是空的，也就是说，还没有进行删除。
• 结果2*

的

• 不是很有用，除了明显的一些数据正在加载（但请注意，第一个id是root_nodes的id，并且存在1）
• 结果3*

的
大约60行后

的

• 明显排序以适合演示（root_nodes id，inner_nodes id，然后是leaf_node id）
• 可以看出，所有3种类型都具有行
• root_nodes 1，3，5，7和9已被报告删除（正如预期的删除是删除奇数ID）。
• 结果4*

的
所有这一切表明，第一行输出是rootnodes id 2，没有rootnodes id 1（或3、5、7或9）的行