Hi,
Is there a way of preventing triggers cascading?
I have a table where I need to set a timestamp field when a new record is
INSERTED. The table uses an auto-increment key field so a BEFORE INSERT trigger
is ruled out. An AFTER INSERT trigger which UPDATEs the new row solves that
problem, but I have a second, AFTER UPDATE, trigger on the table to save an
audit record when the table is updated. I need a way of preventing the AFTER
UPDATE trigger running as a result of the UPDATE command in the AFTER INSERT
trigger.
Is this possible? I thought a SELECT RAISE(IGNORE) in the AFTER INSERT trigger
would do the trick, but it has no effect. I have recursive triggers disabled.
SQL:
CREATE TABLE audit_test_a (
rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
field_1 INTEGER NOT NULL,
field_2 VARCHAR(32) NOT NULL,
field_3 TEXT NOT NULL,
last_changed TIMESTAMP NOT NULL)
CREATE TRIGGER audit_test_a_set_insert_ts AFTER INSERT ON audit_test_a FOR EACH
ROW
BEGIN
UPDATE audit_test_a
set last_changed = strftime('%Y-%m-%d %H:%M:%f','now', 'localtime');
SELECT RAISE(IGNORE);
END
CREATE TRIGGER audit_test_a_audit_update AFTER UPDATE ON audit_test_a FOR EACH
ROW
BEGIN
UPDATE audit_test_a
set last_changed = strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')
WHERE rec_id = old.rec_id;
INSERT INTO change_history VALUES (....);
END
Thanks for your help
--
Peter Hardman
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
