On Sun, Oct 25, 2009 at 5:33 AM, Wanadoo Hartwig
<hartwig.wiesm...@wanadoo.nl> wrote:
> Hi Roger,
>
> I just downloaded SQLite3 version 3.6.19 and did the same testing as I
> did before:
>
> CREATE TABLE Simple (ID integer primary key, Name text);
> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN
> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN
> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
> INSERT INTO Simple (Name) VALUES('one');
> INSERT INTO Simple (Name) VALUES('two');
> DELETE FROM Simple WHERE (ID = 1);
> INSERT INTO Simple (Name) VALUES('three');
> SELECT * FROM Simple;
> SELECT last_insert_rowid() FROM Simple;
>
> This is the output:
>
> 2|two
> 3|three
> 4
> 4
>
> Unfortunately, the output is still not correct because the last rowid
> is 3 as you can see.
>
> Roger: can you please re-open the ticket? This bug only occurs in
> combination with FTS3. Using normal tables in triggers does not cause
> any problems.
>
Yes, I can confirm this bug. I am using Mac OS X.
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Simple (ID integer primary key, Name text);
sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
sqlite> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW BEGIN
...> DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;
sqlite> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW BEGIN
...> INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name); END;
sqlite> INSERT INTO Simple (Name) VALUES('one');
sqlite> INSERT INTO Simple (Name) VALUES('two');
sqlite> SELECT * FROM simple;
ID Name
---------- ----------
1 one
2 two
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
2
sqlite> DELETE FROM Simple WHERE (ID = 1);
sqlite> INSERT INTO Simple (Name) VALUES('three');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
sqlite>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
