Here is the test with the corrected code:
-- character encoding
PRAGMA encoding = 'UTF-16le';
-- Journalling mode
PRAGMA journal_mode=WAL;
-- Automatic ANALYZE
PRAGMA optimize;
CREATE TABLE IF NOT EXISTS A1 (
A1_INDEX VARCHAR(20),
A1_CAT VARCHAR(1) DEFAULT 'W',
A1_STOCK_MIN INTEGER,
A1_STOCK_LEVEL INTEGER,
A1_PRODUCT TEXT,
A1_STATUS NUMBER
CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
CONSTRAINT LOCATION_PK PRIMARY KEY (
A1_INDEX,
A1_CAT
)
-- UNIQUE (
-- A1_INDEX,
-- A1_CAT,
-- ) ON CONFLICT ROLLBACK
);
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
(12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
(17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
(14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
(19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);
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.A1_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;
Here is the test:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 14;
Here is the .selecttrace
'-- SELECT (1/55A1FF75DF48) selFlags=0x0 nSelectRow=0
'-- result-set
'-- AS END
'-- CASE
|-- 1
'-- LIST
|-- EQ fg.af=40000000.n DDL
| |-- DOT fg.af=40000000.n DDL
| | |-- ID "NEW"
| | '-- ID "A1_STATUS"
| '-- 2
'-- CASE
|-- 1
'-- LIST
|-- OR fg.af=40000000.n DDL
| |-- NOT fg.af=40000000.n DDL
| | '-- IN flags=0x40000000
| | |-- DOT fg.af=40000000.n DDL
| | | |-- ID "OLD"
| | | '-- ID "A1_STATUS"
| | '-- LIST
| | |-- 1
| | |-- 8
| | |-- 32
| | '-- 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')
1/0/55A1FF75DF48: after name resolution:
'-- SELECT (1/55A1FF75DF48) selFlags=0x4000c4 nSelectRow=0
'-- result-set
'-- AS END
'-- CASE
|-- 1
'-- LIST
|-- EQ fg.af=40000000.n DDL
| |-- NEW(5)
| '-- 2
'-- CASE
|-- 1
'-- LIST
|-- OR fg.af=40000000.n DDL
| |-- NOT fg.af=40000000.n DDL
| | '-- IN flags=0x40000000
| | |-- OLD(5)
| | '-- LIST
| | |-- 1
| | |-- 8
| | |-- 32
| | '-- 64
| '-- AND fg.af=40000000.n DDL
| |-- NOTNULL fg.af=40000000.n DDL
| | '-- OLD(6)
| '-- NOT fg.af=40000000.n DDL
| '-- EQ fg.af=40000000.n DDL
| |-- OLD(5)
| '-- 4
'-- RAISE abort('Error: someone goofed')
1/0/55A1FF75DF48: Constant propagation not helpful
1/0/55A1FF75DF48: After all FROM-clause analysis:
'-- SELECT (1/55A1FF75DF48) selFlags=0x4000c4 nSelectRow=0
'-- result-set
'-- AS END
'-- CASE
|-- 1
'-- LIST
|-- EQ fg.af=40000000.n DDL
| |-- NEW(5)
| '-- 2
'-- CASE
|-- 1
'-- LIST
|-- OR fg.af=40000000.n DDL
| |-- NOT fg.af=40000000.n DDL
| | '-- IN flags=0x40000000
| | |-- OLD(5)
| | '-- LIST
| | |-- 1
| | |-- 8
| | |-- 32
| | '-- 64
| '-- AND fg.af=40000000.n DDL
| |-- NOTNULL fg.af=40000000.n DDL
| | '-- OLD(6)
| '-- NOT fg.af=40000000.n DDL
| '-- EQ fg.af=40000000.n DDL
| |-- OLD(5)
| '-- 4
'-- RAISE abort('Error: someone goofed')
1/0/55A1FF75DF48: WhereBegin
1/0/55A1FF75DF48: end processing
'-- SELECT (1/55A1FF75DF48) selFlags=0x4000c4 nSelectRow=0
'-- result-set
'-- AS END
'-- CASE
|-- 1
'-- LIST
|-- EQ fg.af=40000000.n DDL
| |-- NEW(5)
| '-- 2
'-- CASE
|-- 1
'-- LIST
|-- OR fg.af=40000000.n DDL
| |-- NOT fg.af=40000000.n DDL
| | '-- IN flags=0x42000000
| | |-- OLD(5)
| | '-- LIST
| | |-- 1
| | |-- 8
| | |-- 32
| | '-- 64
| '-- AND fg.af=40000000.n DDL
| |-- NOTNULL fg.af=40000000.n DDL
| | '-- OLD(6)
| '-- NOT fg.af=40000000.n DDL
| '-- EQ fg.af=40000000.n DDL
| |-- OLD(5)
| '-- 4
'-- RAISE abort('Error: someone goofed')
Based on the .selecttrace I am tempted to conclude that the updated isn't executed. If I check the column the update was executed.
Observations why is are there 4 attempts after the row was selected?