I was able to boil down to a simple, reproducible test case. For intuition: we create a table to hold all values (named "p") and then two tables ("four" and "five") to hold filtered subsets of of the data, automatically populated via trigger on insert.
<verbatim>
CREATE TABLE p (i INTEGER);
CREATE TABLE four (val INTEGER);
CREATE TABLE five (val INTEGER);
CREATE TRIGGER five_trigger AFTER INSERT ON p WHEN NEW.i=5 BEGIN INSERT INTO five (val) VALUES (NEW.i); END;
CREATE TRIGGER four_trigger AFTER INSERT ON p WHEN NEW.i=4 BEGIN INSERT INTO four (val) VALUES (NEW.i); END;
INSERT INTO p (i) VALUES (5);
SELECT * FROM five;
</verbatim>
On version SQLite 3.6.17 (downloaded today from sqlite.org, compiled on OS X 10.5.8) the final SELECT statement returns no rows (incorrect). On version 3.4.0 (apparently pre-installed on OS X) the result is a single row with value of column "val" as the number 5 (correct).
If the order of the CREATE TRIGGER statements is reversed, I get the same (correct) behavior on both versions. Sorry, but I haven't tried any version in between, nor variation on column data types or trigger operations.
<hr><i>drh added on 2009-08-24 00:21:21:</i><br>
Problem introduced by check-in [f099d6773a837dbe4ba85a8fda818e2d9466e743]
on 2008-07-04 09:15:11.
<hr><i>drh added on 2009-08-24 01:37:18:</i><br>
Fixed by check-in [dee1b8eb402f47]
|