How to use or replace 'WITH RECURSIVE' in a SQLite trigger to update a tree-structured table

Question

In SQLite, I have a tree-structured table called layer with columns (id, ..., parentId). And a normal table called dir with columns (id, ..., modifiedTime) .The situation is:

Once some rows in dir are updated with new modifiedTime, all parents of them should also be updated. Guarantee that the modifiedTime is non-decreasing.

Without a trigger, I could use this sql to do it(by using WITH RECURSIVE clause):

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;

But the UPDATE statement makes an error when I put the sql into a trigger. After reading the SQLite offical doc, I got that The WITH clause cannot be used within a CREATE TRIGGER.

That's the issue

How can I make the trigger do what I want?

In another word, how to replace the 'WITH' clause within a trigger?

Answer 1

You can create a recursive trigger (available as SQLite 3.6.18), along the lines of this.

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;

This trigger reacts to changes of modifiedT only and replicates its value on the parent row. The correlated subquery syntax is necessary because NEW always can contain more than one record.

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;

This trigger reacts to changes on any column except modifiedT and sets the current timestamp. In combination they should achieve the wanted effect.

You should create two more triggers that cover INSERT and DELETE and set the parent modifiedT, otherwise adding/removing children will not reflect on parents.

Note that recursive triggers must be enabled at the connection level:

PRAGMA recursive_triggers = ON;