SQLite Forum

Recursive TRIGGER stopping on reference to same table
Login
I'm trying to use recursive triggers, by which I mean `AFTER UPDATE` triggers, which execute an `update-stmt`, which in turn triggers more `AFTER UPDATE` triggers and so forth.

I want to use this to propagate changes in the root of a tree-structure to all descendants. If I did not describe my intentions clearly enough, please let me know; I'll provide an example then.

This seems to work, if changes in table "a" cause changes to table "b" (via the `update-stmt` in the `TRIGGER`), which cause changes to table "c" and so forth. However if a change to a row in table "a" causes a change to another row in table "a", the trigger-chain seems to stop. Here is an example:

```sql
CREATE TABLE a(
    id         INTEGER,
    fromA      INTEGER,
    sharedProp TEXT
);

CREATE TABLE b(
    id         INTEGER,
    fromA      INTEGER,
    sharedProp TEXT
);

CREATE TRIGGER a_sharedProp_updated AFTER UPDATE ON a
BEGIN
    UPDATE a SET sharedProp = NEW.sharedProp WHERE fromA = NEW.id;
    UPDATE b SET sharedProp = NEW.sharedProp WHERE fromA = NEW.id;
END;

INSERT INTO a VALUES (1, NULL, "foo"), (2, 1, "foo");
INSERT INTO b VALUES (1, 2, "foo");
UPDATE a SET sharedProp = "bar" WHERE id = 1;
SELECT sharedProp FROM b;

```

I would expect `SELECT sharedProp FROM b;` to return `bar`, but it returns `foo`.

Have I encountered a bug here? I'm not sure what is happening and could not find the words "recurs*" or "cascade" in https://sqlite.org/lang_createtrigger.html , so I'm not even sure, that my use-case is what TRIGGERs are meant to be used for.