All,
I saw in the documentation that recursive triggers are supported and enabled by
default as of 3.7.0, and as there was no mention of nested triggers I assumed
(wrongly, it would seem) that in sqlite3 the concept of recursive triggers
included nested triggers as well.
In the following script, I have set up a few triggers for different purposes.
Because I don't want the triggers to cascade to one-another, I set PRAGMA
recursive_triggers = 0. As shown in the log, however, this isn't working as I
wanted and after the DELETE operation, the delete trigger performs an UPDATE
operation, which in turn results in an UPDATE trigger being run once for each
row that was updated by the DELETE trigger.
How can I control this such that one trigger's statement won't cause another
trigger to run?
-Regards,
-Jay
----------------------
PRAGMA recursive_triggers = 0;
CREATE TABLE test (val INTEGER NOT NULL);
CREATE TABLE log (timestamp, message);
CREATE TRIGGER trig_update_before_insert BEFORE INSERT ON test
BEGIN
INSERT INTO log VALUES (datetime('NOW'), 'Trigger: trig_update_before_insert,
new.val='||new.val);
UPDATE test SET val = val + 1 WHERE val >= new.val;
END;
CREATE TRIGGER IF NOT EXISTS trig_update_after_delete AFTER DELETE ON test
BEGIN
INSERT INTO log VALUES (datetime('NOW'), 'Trigger: trig_update_after_delete,
old.val='||old.val);
UPDATE test SET val = val - 1 WHERE val > old.val;
END;
CREATE TRIGGER IF NOT EXISTS trig_update_before_update_lower BEFORE UPDATE ON
test WHEN new.val > old.val
BEGIN
INSERT INTO log VALUES (datetime('NOW'), 'Trigger ran:
trig_update_before_update_lower, new.val='||new.val||', old.val='||old.val);
UPDATE test SET val = val - 1 WHERE val > old.val AND val <= new.val;
END;
CREATE TRIGGER IF NOT EXISTS trig_update_before_update_raise BEFORE UPDATE ON
test WHEN new.val < old.val
BEGIN
INSERT INTO log VALUES (datetime('NOW'), 'Trigger ran:
trig_update_before_update_raise, new.val='||new.val||', old.val='||old.val);
UPDATE test SET val = val + 1 WHERE val >= new.val AND val < old.val;
END;
INSERT INTO test VALUES (1);
INSERT INTO test VALUES (2);
INSERT INTO test VALUES (3);
DELETE FROM test WHERE val = 1;
SELECT * FROM test ORDER BY val;
SELECT * FROM log;
~/mipsbin/priority_continuity_testing # ./sqlite378_xip < test.sql
1
2
2013-11-08 00:03:42|Trigger: trig_update_before_insert, new.val=1
2013-11-08 00:03:42|Trigger: trig_update_before_insert, new.val=2
2013-11-08 00:03:42|Trigger: trig_update_before_insert, new.val=3
2013-11-08 00:03:42|Trigger: trig_update_after_delete, old.val=1
2013-11-08 00:03:42|Trigger ran: trig_update_before_update_raise, new.val=1,
old.val=2
2013-11-08 00:03:42|Trigger ran: trig_update_before_update_raise, new.val=2,
old.val=3
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
