See: https://www.sqlite.org/mark/lang_createtrigger.html?Cautions&The+val*ger.#mark
On 5/24/15, Tomash Brechko <tomash.brechko at gmail.com> wrote: > Hello, > > The following code > > -- beg -- > CREATE TEMP TABLE t (pk INTEGER PRIMARY KEY, i); > CREATE TEMP TABLE b (before_pk, i); > CREATE TEMP TABLE a (after_pk, i); > > CREATE TEMP TRIGGER tb BEFORE INSERT ON t FOR EACH ROW > BEGIN > INSERT INTO b VALUES (NEW.pk, NEW.i); > END; > > CREATE TEMP TRIGGER ta AFTER INSERT ON t FOR EACH ROW > BEGIN > INSERT INTO a VALUES (NEW.pk, NEW.i); > END; > > INSERT INTO t VALUES (-1, -1); > INSERT INTO t VALUES (0, 0); > INSERT INTO t VALUES (NULL, 1); > INSERT INTO t (i) VALUES (2); > > .header on > .mode column > .width -2 -9 -8 > SELECT pk, before_pk, after_pk > FROM t NATURAL JOIN b NATURAL JOIN a; > -- end -- > > with SQLite 3.8.10.2 (also 3.8.9 and possibly earlier) produces > > -- beg -- > pk before_pk after_pk > -- --------- -------- > -1 -1 -1 > 0 0 0 > 1 -1 1 > 2 -1 2 > -- end -- > > As you can see BEFORE INSERT trigger observes -1 in NEW.pk for INTEGER > PRIMARY KEY field when NULL is passed explicitly or implicitly. Such magic > -1 makes it impossible to distinguish in BEFORE INSERT trigger the NULL (a > command to generate new PK) from explicit -1 (which is a valid value for > integer PK). I couldn't find a description of this feature in either > https://www.sqlite.org/autoinc.html (section Background), > https://www.sqlite.org/lang_createtable.html#rowid or > https://www.sqlite.org/lang_createtrigger.html , so I consider this a bug > (expect to see NULL in NEW.pk for the last two inserts). > > -- > Tomash Brechko > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
