That been said I have removed all of the references to table C1 in the A100 trigger.
Then I've pondered a while, about what you have said and I modified the script to demonstrate that the optimizer function is greedy and that the optimizer will also attempt to create code for triggers that will not be triggered because the status isn't complying to the condition.
So I executed my test:
.selecttrace 0xfffff
--.wheretrace
BEGIN TRANSACTION;
UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;
END;
Which resulted in an error:
Error: near line 6: no such column: C1_QTY_PRODUCED
I assume that if I get an error then the "trigger must have been triggered".
By the way: I have made the same error in the C110 trigger. Apparently, there is a reason in my madness…
If I update the status to 2 for table A1 then I think that following unfolds:
a) The condition is valid for trigger A100 → To proceed to generate the code;
b) The condition is invalid for trigger A101 → To do nothing and ignore the trigger.
-- character encoding
PRAGMA encoding = 'UTF-16le';
PRAGMA foreign_keys = TRUE;
-- Journalling mode
PRAGMA journal_mode=WAL;
-- Automatic ANALYZE
PRAGMA optimize;
CREATE TABLE IF NOT EXISTS _Variables (NAME TEXT PRIMARY KEY, VALUE TEXT)
WITHOUT ROWID;
-- STATUS
-- 1 → IN STOCK
-- 2 → NEW STOCK IN PRODUCTION
-- 4 → BLOCKED STOCK
-- 128 → PRODUCT NO LONGER PRODUCED
CREATE TABLE IF NOT EXISTS A1 (
A1_INDEX INTEGER,
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 A1_PK PRIMARY KEY (
A1_INDEX,
A1_CAT
),
UNIQUE (
A1_INDEX
)
);
-- PRODUCTION STATUS:
-- 1 → PREPARED FOR PRODUCTION
-- 2 → IN PRODUCTIION
-- 4 → PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 → PLANNING PROCESSING
-- 2 → PROCESSING
-- 4 → EVALUTION
-- 8 → PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
C1_PRODUCTION_ORDER VARCHAR(20),
C1_INDEX INTEGER,
C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
C1_QTY_PRODUCED NUMBER DEFAULT 0,
C1_PRODUCTION_STATUS NUMBER
CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
C1_STATUS NUMBER
CHECK (C1_STATUS IN (1, 2, 4, 8)),
C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
CONSTRAINT C1_PK PRIMARY KEY (
C1_PRODUCTION_ORDER
) ON CONFLICT ROLLBACK,
CONSTRAINT C1_INDEX_FK FOREIGN KEY (
C1_INDEX
)
REFERENCES A1 (
A1_INDEX
),
UNIQUE (
C1_INDEX
)
);
-- Create stock
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_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
INSERT INTO _Variables VALUES
('NEW_A1_STATUS', NEW.A1_STATUS),
('OLD_A1_STATUS', OLD.A1_STATUS);
SELECT CASE 1
WHEN (NEW.A1_STATUS == 2)
THEN CASE 1
WHEN (NOT (OLD.A1_STATUS IN (1, 128))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
THEN RAISE (ABORT, 'Error: someone goofed')
-- WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) != 4)
-- THEN RAISE (ABORT, 'Error you are not allowed to change the status')
END
END
END;
END;
CREATE TRIGGER IF NOT EXISTS A1_101 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS == 4)
BEGIN
-- Signal processing
UPDATE OR ROLLBACK C1 SET
C1_STATUS = NEW.A1_STATUS
WHERE C1_INDEX == NEW.A1_INDEX;
END;
-- Let us make sausages
CREATE TRIGGER IF NOT EXISTS C1_101 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 2)
BEGIN
SELECT CASE 1
WHEN (OLD.C1_STATUS != 1)
THEN RAISE (ABORT, 'Error, you need to plan the production first')
WHEN (SELECT A1_STATUS FROM A1 WHERE A1_INDEX == NEW.C1_INDEX) IN (2, 4, 128)
THEN RAISE (ABORT, 'Error, production order is invalid')
END;
--Keep the status in sync
-- Signal new stock in production
UPDATE OR ROLLBACK A1 SET
A1_STATUS = 2
WHERE A1_INDEX == NEW.C1_INDEX;
END;
- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_110 BEFORE UPDATE ON C1
WHEN (NEW.C1_PRODUCTION_STATUS == 8)
BEGIN
SELECT CASE 1
WHEN (NEW.C1_QTY_PRODUCED == 0)
THEN RAISE (ABORT, 'Error: someone goofed')
WHEN (NEW.C1_QTY_TO_PRODUCE == 0)
THEN RAISE (ABORT, 'Error there no sausages are ordered!')
END;
UPDATE OR ROLLBACK A1 SET
A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
WHERE A1_INDEX = NEW.C1_INDEX;
UPDATE OR ROLLBACK C1 SET
C1_QTY_TO_PRODUCE = 0,
C1_QTY_PRODUCED = 0,
C1_STATUS = 1,
C1_PRODUCTION_STATUS = 1 -- CLEAN AND RESET THE PRODUCTION CHAIN
WHERE C1_INDEX == NEW.C1_INDEX;
-- SYNC THE STATUS
UPDATE OR ROLLBACK A1 SET
A1_STATUS = (SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.C1_INDEX)
WHERE A1_INDEX = NEW.C1_INDEX;
END;
-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
(12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
(19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);