On Sun, Apr 8, 2012 at 9:25 PM, Simon Slavin <[email protected]> wrote: > On 9 Apr 2012, at 2:14am, Stephan Beal <[email protected]> wrote: > >> Is it reliable/portable/well-defined to use (read-only) the sqlite_sequence >> table to find the last insertion ID for a given table? i know about >> sqlite3_last_insert_id(), but i'm wondering if reading the sqlite_sequence >> table directly might be safer vis-a-vis triggers which perform an insert >> (and thereby cause sqlite3_last_insert_id() to possibly return an >> unexpected value). > > The C function sqlite3_last_insert_id(), and the SQLite wrapper around it > last_insert_rowid() are as good as it gets. You do have to be a little > careful if you have a TRIGGER on an INSERT which does another INSERT. If you > use TRIGGERs I suggest you test out the behaviour for yourself to figure out > what it does.
Interference of sqlite3_last_insert_id() with triggers is well documented and doesn't have any inconvenient caveats. See 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." So I'm not sure what problems do you see with using sqlite3_last_insert_id() and triggers even if they issue another bunch of inserts. Pavel _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
