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*".