Recursive TRIGGER stopping on reference to same table
(1) By Richard Ulmer (codesoap) on 2020-04-26 10:13:27 [link]
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.
(2) By Keith Medcalf (kmedcalf) on 2020-04-26 12:08:54 in reply to 1 [link]
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
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*".