On 04/23/2014 05:05 PM, Peter Hardman wrote:
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
You could try:
CREATE TRIGGER audit_test_a_audit_update
AFTER UPDATE OF (all-columns-except-last_changed) ON ...
Dan.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
