Thx.For the comment. That's precisely what I am doing. Updating the record which will be updated in a before update trigger causes either infinite recursion or "undefined behaviour. —> valid argument. So here is the present version -- character encoding PRAGMA encoding = 'UTF-16le'; -- Journalling mode PRAGMA journal_mode=WAL; -- Automatic ANALYZE PRAGMA optimize; -- 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 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 A1_PK PRIMARY KEY ( A1_INDEX, A1_CAT ) -- UNIQUE ( -- A1_INDEX, -- A1_CAT, -- ) ON CONFLICT ROLLBACK ); -- 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_INDEX VARCHAR(20), C1_PRODUCTION_ORDER VARCHAR(20), 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_INDEX ) ON CONFLICT ROLLBACK, CONSTRAINT C1_INDEX_FK FOREIGN KEY ( C1_INDEX ) REFERENCES A1 ( A1_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 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; -- Signal processing UPDATE OR ROLLBACK C1 SET C1_STATUS = NEW.A1_STATUS WHERE C1_INDEX == NEW.A1_INDEX; END; -- Let us make sauceges 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_PRODUCTIONS_STATUS == 8) BEGIN SELECT CASE 1 WHEN (NEW.C1_QUANTITY_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);