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
