After implementing simple auditing of table changes, last_insert_rowid()
still provides the value I really want even though additional table inserts
are being performed in the background. To illustrate:
CREATE TABLE t (a);
CREATE TABLE t_log (
timestamp DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
a
);
CREATE TRIGGER insert_t AFTER INSERT ON t
BEGIN
INSERT INTO t_log (action, a) VALUES ('INSERT', new.a);
END;
CREATE TRIGGER update_t AFTER UPDATE ON t
BEGIN
INSERT INTO t_log (action, a) VALUES ('UPDATE', new.a);
END;
CREATE TRIGGER delete_t BEFORE DELETE ON t
BEGIN
INSERT INTO t_log (action, a) VALUES ('DELETE', old.a);
END;
Performing an insert & updates yields:
sqlite> insert into t (a) values (0);
sqlite> select last_insert_rowid();
last_insert_rowid()
1
sqlite> update t set a = 1;
sqlite> select last_insert_rowid();
last_insert_rowid()
1
sqlite> select * from t_log;
timestamp|action|a
2011-10-21 18:13:38|INSERT|0
2011-10-21 18:14:18|UPDATE|1
sqlite>
This begs two questions.
Because of the trigger's insertion into the second table, should not the
second select statement return a value of 2 instead of 1 since the last
insertion occurred in table t_log?
Secondly, does anyone have suggestions or recommendations on books (or
otherwise) on triggers? I suspect the behavior seen here is expected, but I
haven't found a source which addresses trigger behavior.
Thank you for any candor shared.
Jim
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
