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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
