SQLite：如何删除固定高度树中从根到叶的节点？

我们在SQLite中采用三个表来表示树结构：

CREATE TABLE IF NOT EXISTS "root_nodes" (
    "id"    INTEGER NOT NULL,
    -- 其他字段
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "inner_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL,
    -- 其他字段
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "leaf_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL,
    -- 其他字段
    PRIMARY KEY("id")
);

示例中的树高度为3层（根节点层、内层节点层、叶子节点层），实际场景中可能存在多个内层节点层，但层数是固定的。

我们的需求是：给定一个根节点ID，删除该节点所有后代的内层节点和叶子节点，同时返回被删除的叶子节点ID，理想状态下希望用单条SQL语句完成。

我最初尝试用嵌套DELETE语句实现：

DELETE FROM leaf_nodes WHERE parent IN (
    DELETE FROM inner_nodes WHERE parent IN (
        DELETE FROM root_nodes WHERE id = ? RETURNING id
    ) RETURNING id
) RETURNING id

但后来了解到，SQLite的RETURNING子句仅支持在顶层的DELETE、INSERT和UPDATE语句中使用，因此这个嵌套方案无法生效。

目前我们通过触发器来实现删除逻辑，但触发器无法收集并返回被删除的叶子节点ID。

内容的提问来源于stack exchange，提问作者Peter Jankuliak