D. Richard Hipp wrote:
>
> I think the FTS virtual table is doing the INSERTs inside its xCommit
> method, after the trigger has been exited. So the mechanism that
> resets the last_insert_rowid when a trigger exits does not apply since
> the trigger has already existed by the time the FTS virtual table does
> its INSERT. (NB: I have not verified this in a debugger - it is just
> my theory.)
>
It looks like you are correct. If the update happens in an explicit
transaction then the last_insert_rowid value is not changed until after
the explicit transaction is commited.
insert into one (value) values ("hello1");
select last_insert_rowid(); -- returns 1
begin;
insert into one (value) values ("hello2");
select last_insert_rowid(); -- returns 2
update one set value="hello3" where id=1;
select last_insert_rowid(); -- still returns 2
commit;
select last_insert_rowid(); -- now returns 3
This also seems to indicate a problem. It seems even less correct for a
commit to change the value of the last insert rowid.
I can see how this may be complicated to correct unless the lastrowid
and nchanges values are saved and restored around the xCommit calls that
happen when the active transaction ends. Would that be a possible solution?
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
