> 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?
http://www.sqlite.org/c3ref/last_insert_rowid.html "If an INSERT occurs within a trigger or within a virtual table method, then this routine will return the rowid of the inserted row as long as the trigger or virtual table method is running. But once the trigger or virtual table method ends, the value returned by this routine reverts to what it was before the trigger or virtual table method began." > 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. Why do you want to know last inserted rowid for something happened in the trigger, i.e. for something you didn't explicitly requested to do? Pavel On Fri, Oct 21, 2011 at 2:22 PM, James Hartley <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
