CODEWITHSUNDEEP
sqlsqlite
Luis
Luis

Reputation: 11

How to use RECURSIVE TRIGGERS in SQLITE

In SQLITE I have a tree structured table called Treeview with colimns (Tag, Name, Checked, FatherTag). Bassically what I want to do is to UPDATE the Checked Father element when all the child elements are Checked.

To do that I have a trigger that starts when I begin my recursive query. This trigger it's supposed to modify the Checked column when it has been UPDATED.

CREATE TRIGGER tr_update_father_Checked
         AFTER UPDATE OF Checked
            ON Treeview
BEGIN
    UPDATE Treeview
       SET Checked = (
               SELECT Checked
                 FROM NEW
                WHERE Tag = Treeview.Tag
           )
     WHERE Tag IN (
        SELECT FatherTag
          FROM NEW
    );
END;

Once i have my trigger on, i run my recursive query to select one familly of a tree and modify a Checked element.

    WITH RECURSIVE cte_Treeview (
    Tag,
    DisplayedName,
    FatherTag,
    NodeIndex,
    Checked
)
AS (
    SELECT t.Tag,
           t.DisplayedName,
           t.FatherTag,
           t.NodeIndex,
           t.Checked
      FROM Treeview t
     WHERE t.Tag = 1
    UNION ALL
    SELECT t.Tag,
           t.DisplayedName,
           t.FatherTag,
           t.NodeIndex,
           t.checked
      FROM Treeview t
           JOIN
           cte_Treeview c ON c.Tag = t.FatherTag
     LIMIT 12
)

UPDATE Treeview
SET "Checked" = "R"
WHERE Tag = 2;

And I am getting a:

No such a table : main.NEW

Can some one please tell me what am I doing wrong?

Thank you.

Upvotes: 0

Views: 552

Answers (1)

Shawn
Shawn

Reputation: 52499

First off, to use a recursive trigger, you have to enable it on every database connection (Just like foreign keys):

PRAGMA recursive_triggers = on;

Second, you're trying to use NEW like it is a table. It's not. As the documentation says,

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant... [See documentation for those].

They refer to the row being changed, not a table.

So something like (Untested due to lack of sample table and data):

CREATE TRIGGER tr_update_father_Checked
         AFTER UPDATE OF Checked
            ON Treeview
BEGIN
    UPDATE Treeview SET Checked = NEW.Checked WHERE Tag = NEW.FatherTag;
END;

Upvotes: 2

Related Questions