sql—如何在sqlite触发器中使用或替换“with recursive”来更新树结构表

7y4bm7vi 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(352)

在sqlite中，我有一个树结构的表，名为 layer 带列 (id, ..., parentId) . 还有一张普通的table叫 dir 带列 (id, ..., modifiedTime) .情况是：
一旦有几排 dir 如果使用新的modifiedtime更新，则还应更新它们的所有父级。保证修改时间不减少。
如果没有触发器，我可以使用这个sql来完成（通过使用with recursive子句）：

WITH RECURSIVE P(id) AS (
     SELECT parentId FROM layer WHERE id="The Id of Modified Row"
     UNION ALL
     SELECT L.parentId FROM layer AS L, P WHERE L.id=P.id)
UPDATE dir SET modifiedT=CURRENT_TIMESTAMP WHERE id IN P;

但是update语句在我将sql放入触发器时出错。在阅读了sqlite官方文档之后，我明白了 The WITH clause cannot be used within a CREATE TRIGGER. ####这就是问题所在
我怎么能让扳机按我的意思动呢？
换言之，如何替换触发器中的“with”子句？

sql sqlite triggers tree-structure

来源：https://stackoverflow.com/questions/63147743/how-to-use-or-replace-with-recursive-in-a-sqlite-trigger-to-update-a-tree-stru

1条答案

按热度按时间

shstlldc1#

您可以创建一个递归触发器（sqlite3.6.18版本），如下所示。

CREATE TRIGGER tr_update_parent_modifiedT
AFTER UPDATE OF modifiedT ON layer
BEGIN
  UPDATE
    layer
  SET
    modifiedT = (SELECT modifiedT FROM NEW WHERE id = layer.id)
  WHERE
    id IN (SELECT parentId FROM NEW);
END;

此触发器对 modifiedT 仅在父行上复制其值。相关子查询语法是必需的，因为 NEW 始终可以包含多个记录。

CREATE TRIGGER tr_update_self_modifiedT
AFTER UPDATE OF parentId, name, all, other, columns, except_modifiedT ON layer
BEGIN
  UPDATE
    layer
  SET
    modifiedT = CURRENT_TIMESTAMP
  WHERE
    id IN (SELECT id FROM NEW);
END;

此触发器对除 modifiedT 并设置当前时间戳。它们的结合应该达到预期的效果。
你应该再创建两个触发器 INSERT 以及 DELETE 并设置父对象 modifiedT ，否则添加/删除子项将不会反映到家长身上。
请注意，必须在连接级别启用递归触发器：

PRAGMA recursive_triggers = ON;

赞(0）回复(0）举报 2021-07-29

我来回答

sql—如何在sqlite触发器中使用或替换“with recursive”来更新树结构表

1条答案

相关问题

热门标签

最新问答