The syntax error in trigger C1_100 should have been detected while SQLite is building the database;
I would guess the SQL in a trigger only gets compiled/prepared when they come into play. Similar to how missing columns in views don't get complained about until you try to access the view.
—> The assumption that SQLite only scans the triggers if they come into play not correct. SQLite parses everything and stores the trigger metadata in a system table. Checking that the variables are valid names and existing column names is easy and would improve the trustworthiness of the SQLite build process.
The .selecttrace was initially fixed but after update Ubuntu, the command is no longer working;
Presumably, the Ubuntu update replaced your SQLite binary with a newer version. From the Debugging Hints page, since you need to compile with -DSQLITE_ENABLE_SELECTTRACE, and this isn't a documented (or officially supported option), the new version presumably doesn't have it enabled (my off-the-shelf Windows version doesn't).
—> Nope. SQLite wasn't updated. I am running a debug version as was instructed by Mr. Hipp in the local directory. ./sqlite3 …
Why is SQLite scanning also trigger C_100 when update affects only table A1.
Trigger C1_100 (presumably C_100 is a typo) is defined as BEFORE UPDATE ON A1 ... why would you expect SQLite not to process this trigger?
—> Oops, I goofed up. I am trying to build an example to reproduces a number of things related with the optimizer function and for that I need .selecttrace.
So I changed the A1 to C1 as it should have been and executed test_a2.sql.
Success! Nice! Let us repeat test_a2 setting the status to 2.
So the modified test is:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;
This fails because OLD.A1_STATUS and NEW.A1_STATUS are the same. The Old status is 1 and the new status is 2.If I could execute .selecttrace you would have seen that old and new have the same value.