SQLite Forum

.selecttrace unknown and strange errors
Login
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);