时间戳

首页 > 解决方案 > 如何在 SQLite 触发器中使用或替换“WITH RECURSIVE”来更新树结构表

sql - 如何在 SQLite 触发器中使用或替换“WITH RECURSIVE”来更新树结构表

问题描述

layer在 SQLite 中，我有一个名为columns的树形结构表(id, ..., parentId)。和一个普通的表称为dir列(id, ..., modifiedTime)。情况是：

一旦使用新的 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;

但是当我将 sql 放入触发器时，UPDATE 语句会出错。阅读SQLite 官方文档后，我明白了The WITH clause cannot be used within a CREATE TRIGGER.

这就是问题

我怎样才能让触发器做我想做的事？

换句话说，如何替换触发器中的“WITH”子句？

标签： sqlsqlitetriggerstree-structure

解决方案

您可以按照此思路创建递归触发器（可作为 SQLite 3.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;

推荐阅读