On Jun 22, 2009, at 3:33 PM, Oliver Peters wrote: > Hello out there, > > > to my mind I get false entries in sqlite_sequence using this code: > > > CREATE TABLE IF NOT EXISTS a( > id INTEGER PRIMARY KEY AUTOINCREMENT, > code VARCHAR NOT NULL, > name VARCHAR NOT NULL, > identity INTEGER DEFAULT NULL, > creator INTEGER DEFAULT NULL, > timestamp TIMESTAMP DEFAULT > (datetime('now','localtime')) > ); > > > CREATE TRIGGER IF NOT EXISTS test > BEFORE INSERT ON "a" > BEGIN > INSERT INTO a(code,name,identity) > VALUES(new."code",new."name",new."identity"); > SELECT RAISE(IGNORE); > END; > > > INSERT INTO a(code,name,identity) VALUES('V','abc',1); > INSERT INTO a(code,name,identity) VALUES('S','def',1); > > > As a result I get 2 records in sqlite_sequence: > > name seq > a 1 > a NULL > > shouldn't there only 1 record (a 2)?
The ROWID is not generated until the INSERT statement actually runs. Hence the BEFORE trigger does not have access to it and the BEFORE trigger sees a NULL. Change the trigger to an AFTER trigger and it will work. > > The ids in table a are as expected (1 & 2). > > > ______________________________________________________ > GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! > Jetzt freischalten unter http://movieflat.web.de > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users