And then I changed the A1_STATUS to 1 which should ignore the trigger after testing the condition.
Here is the test:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 1
WHERE A1_INDEX == 14;
Examining the .selecttrace log I discovered that the SQLite optimize-function attempts to execute((?) or scan(?) the trigger. So I made an error in the trigger to proof this:
CREATE TRIGGER IF NOT EXISTS A1_STOCK_UPDATE BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
SELECT CASE 1
WHEN (NEW.A2_STATUS == 2)
THEN CASE 1
WHEN (NOT (OLD.A1_STATUS IN (1, 8, 32, 64))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
THEN RAISE (ABORT, 'Error: someone goofed')
END
END
END;
END;
The error (only the final part of the .selecttrace is shown) proofs that the optimize function scans the triggers and halts the update even if the trigger is not part of the equation.
Here is the final part of the .selecttrace log
| | '-- 64
| '-- AND fg.af=40000000.n DDL
| |-- NOTNULL fg.af=40000000.n DDL
| | '-- DOT fg.af=40000000.n DDL
| | |-- ID "OLD"
| | '-- ID "A1_REF"
| '-- NOT fg.af=40000000.n DDL
| '-- EQ fg.af=40000000.n DDL
| |-- DOT fg.af=40000000.n DDL
| | |-- ID "OLD"
| | '-- ID "A1_STATUS"
| '-- 4
'-- RAISE abort('Error: someone goofed')
Error: near line 3: no such column: NEW.A2_STATUS