SQLite Forum

Recursive TRIGGER stopping on reference to same table
Login

Recursive TRIGGER stopping on reference to same table

(1) By Richard Ulmer (codesoap) on 2020-04-26 10:13:27 [source]

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:

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.

(2) By Keith Medcalf (kmedcalf) on 2020-04-26 12:08:54 in reply to 1 [link] [source]

Did you turn on recursive triggers either by compiling your SQLite3 library with SQLITE_DEFAULT_RECURSIVE_TRIGGERS defined as 1 or using pragma recursive_triggers=1 on the connection?

https://sqlite.org/pragma.html#pragma_recursive_triggers

sqlite> CREATE TABLE a
   ...> (
   ...>     id         INTEGER,
   ...>     fromA      INTEGER,
   ...>     sharedProp TEXT
   ...> );
sqlite>
sqlite> CREATE TABLE b
   ...> (
   ...>     id         INTEGER,
   ...>     fromA      INTEGER,
   ...>     sharedProp TEXT
   ...> );
sqlite>
sqlite> 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;
sqlite>
sqlite> INSERT INTO a VALUES (1, NULL, 'foo'), (2, 1, 'foo');
sqlite> INSERT INTO b VALUES (1, 2, 'foo');
sqlite> UPDATE a SET sharedProp = 'bar' WHERE id = 1;
sqlite> SELECT sharedProp FROM b;
bar

As an aside, can you please use proper quotes?

Text strings are delimited with single-quote ('), not double-quote ("). Double-quote is used to delimit identifiers (column names, table names, index names, trigger names, and so on and so forth).

(3) By Richard Ulmer (codesoap) on 2020-04-26 12:45:52 in reply to 2 [link] [source]

With pragma recursive_triggers=1 everything works flawlessly. Thanks a lot! I hadn't even thought about PRAGMAs...

As an aside, can you please use proper quotes?

I sure can. Thanks for making me aware of my quote-abuse :-)

P.S.: I think it might be a good idea to link the info about recursive_triggers on https://sqlite.org/lang_createtrigger.html . This way I would have found the solution myself when I searched the page for "recurs*".