I need to use recursive triggers. In some cases I want to "normalize"
values of some columns of NEW being INSERTed or UPDATEd, but there's no
UPDATE syntax for changing NEW, thus I can't write something like:
CREATE TRIGGER ...
BEGIN
UPDATE SET NEW.somecol = (<expression to normalize NEW.somecol>);
END;
I must write:
CREATE TRIGGER ...
BEGIN
UPDATE <table> SET somecol = (<expression to normalize NEW.somecol>) WHERE
rowid = NEW.rowid;
END;
And such triggers have to be AFTER triggers too, since the row to update
wouldn't exist in the BEFORE INSERT case...
But the real problem is that my triggers will just recurse infinitely,
since I need both, AFTER INSERT and AFTER UPDATE triggers. The AFTER
INSERT trigger will trigger the AFTER UPDATE trigger, and that one will
trigger itself, recursing ad naseum. This [obviously] happens in the
case of UPDATEs too.
I need a way to break this recursion.
One idea I'm considering is to have two columns where I have one: one
that the application sets, and one that the triggers set, with the
application selecting the latter in its queries. This will work, but it
seems too complicated (the app sets one column but WHERE clauses must
use the other, and SELECTs must fetch the other as well), and will waste
space on disk (I probably don't care about that).
Another idea is that I could use temp triggers and temp tables instead,
and have the app do inserts into the temp tables and them have triggers
that make suitable changes to the actual DB (INSERT OR REPLACE) and not
triggers on the actual DB. This too will work, but now the app has to
do INSERTs exclusively, and on a temp table, and it has to execute
statements to create the temp triggers at DB open time. Or I might make
that additional table not a temp table and just delete all rows from it
before COMMITing (it'd be nice to have transaction-level triggers to
automate such cleanup).
Either way though the need to break infinite trigger recursion affects
the "interface" seen by the application in obnoxious ways. Maybe I've
missed a better way to break trigger recursion that wouldn't have this
problem? What might that be? I tried using pragma to toggle trigger
recursivity inside the trigger bodies, but this, unsurprisingly, didn't
work. A brief search does not turn up generic trigger recursion
techniques that don't rely on DBMS-specific extensions.
Even if I missed some technique, it might be useful to be able to
declare that a given INSERT/UPDATE/DELETE statement in a trigger must
not recurse. It would also be nice to have UPDATE syntax for updating
the row in question (NEW), which would not recurse.
Also, it'd be convenient to have a sticky recursive_triggers pragma, as
otherwise one has to execute a pragma statement on every DB open.
Any help would be much appreciated. Thanks, and happy New Year,
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
