Its the bytre code I want to read.
Try this example:
-- 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 (A1_STATUS = 2) AND (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;
~
~
It will not work and result in all sorts of erros