Introduction to the problem
SQLite has a number of issues that challenges a programmer to control how the database processes data with triggers. To illustrate the behavior of the optimizer function I’ve created a script with a deliberate error in one of the triggers. The script creates two tables and two update triggers. Here is the script a2.sql:
-- 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 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, 8, 32, 64))) 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) == 1)
                                        THEN RAISE (ABORT, 'Error you are not allowed to change the status')
                                END
                        END
                END;

        UPDATE OR ROLLBACK A1 SET
                A1_STATUS = NEW.A1_STATUS
        WHERE A1_INDEX = NEW.A1_INDEX;

END;

-- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_100 BEFORE UPDATE ON A1
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;
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);
The trigger  C1_100 has a syntax error. The variable NEW.C1_PRODUCTIONS_STATUS doesn’t exist. This deliberate error is part of the test. 
Build the database with the following command:
sqlite3 a2.db <  a2.sql                                                             
∎
Test a1.sql
This test updates the product status in table A1 to indicate that the production is being planned. The script for test_a1.sql looks like this:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 1 
WHERE A1_INDEX == 14;
Execute the script as follows:
sqlite3 a2.db < test_a1.sql | tee result_test_a1.txt
SQLite abends the execution of the test and shows the following freaky error messages:
Error: unknown command or invalid arguments:  "selecttrace". Enter ".help" for help
Error: near line 3: no such column: NEW.C1_PRODUCTIONS_STATUS
∎

Observations
I’ve made the following observations:
    • The syntax error in trigger C1_100 should have been detected while SQLite is building the database;
    • The .selecttrace was initially fixed but after update Ubuntu the command is no longer working;
    • Why is SQLite scanning also trigger C_100 when an update effects only table A1.

The syntax error in trigger C1_100 should have been detected while SQLite is building the database;

I would guess the SQL in a trigger only gets compiled/prepared when they come into play. Similar to how missing columns in views don't get complained about until you try to access the view.
—> The assumption that SQLite only scans the triggers if they come into play not correct. SQLite parses everything and stores the trigger metadata in a system table. Checking that the variables are valid names and existing column names is easy and would improve the trustworthiness of the SQLite build process.


    The .selecttrace was initially fixed but after update Ubuntu, the command is no longer working;

Presumably, the Ubuntu update replaced your SQLite binary with a newer version. From the Debugging Hints page, since you need to compile with -DSQLITE_ENABLE_SELECTTRACE, and this isn't a documented (or officially supported option), the new version presumably doesn't have it enabled (my off-the-shelf Windows version doesn't).

—> Nope. SQLite wasn't updated. I am running a debug version as was instructed by Mr. Hipp in the local directory. ./sqlite3 …
   

    Why is SQLite scanning also trigger C_100 when update affects only table A1.
  
Trigger C1_100 (presumably C_100 is a typo) is defined as BEFORE UPDATE ON A1 ... why would you expect SQLite not to process this trigger?

—> Oops, I goofed up. I am trying to build an example to reproduces a number of things related with the optimizer function and for that I need .selecttrace.

So I changed the A1 to C1 as it should have been and executed test_a2.sql.
Success! Nice! Let us repeat test_a2 setting the status to 2.
So the modified test is:
.selecttrace 0xfffff

UPDATE A1 SET
A1_STATUS = 2
WHERE A1_INDEX == 19;

This fails because OLD.A1_STATUS  and NEW.A1_STATUS  are the same. The Old status is 1 and the new status is 2.If I could execute .selecttrace you would have seen that old and new have the same value.

I fail to reproduce the error with OLD and NEW. The Ubuntu update has changed something.
Time to figure out what has changed.

First step rebuild sqlite from snapshot 202006241145 using this script:
usage="Usage : '$0 ' [-ahbd]"

if [[ $1 =~ (-[abdh]) ]]; then
        echo "--------------"
else
        echo "Not a valid option is given!"
        echo $Usage
        exit 1
fi

while getopts "abdh" opt; do
  case ${opt} in
    h )
        echo $usage
        echo "Options:"
        echo "    -b            Compile without debug options"
        echo "    -d            Compile with debug options"
        echo "    -h            This menu for help"
        exit 0
        ;;
    a )
        echo "Building a debug version of SQLite3 with all options"
        ./configure --enable-debug --enable-all
        make
        exit 0
       ;; 
    b )
        echo "Building SQLite3 without debug options"
        export CFLAGS="-fPIC \
                -DSQLITE_ENABLE_FTS3  \
                -DSQLITE_ENABLE_FTS3_PARENTHESIS  \
                -DSQLITE_ENABLE_FTS4 \
                -DSQLITE_ENABLE_FTS5 \
                -DSQLITE_ENABLE_JSON1 \
                -DSQLITE_ENABLE_LOAD_EXTENSION \
                -DSQLITE_ENABLE_RTREE \
                -DSQLITE_ENABLE_STAT4 \
                -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
                -DSQLITE_TEMP_STORE3 \
                -DSQLITE_USE_URI \
                -O2 "
        ;;
    d )
        echo "Bulding SQLite3 with debug options"
        export SQLITE3_CFLAGS="-fPIC \
                -DSQLITE_ENABLE_FTS3 \
                -DSQLITE_ENABLE_FTS3_PARENTHESIS  \
                -DSQLITE_ENABLE_FTS4 \
                -DSQLITE_ENABLE_FTS5 \
                -DSQLITE_ENABLE_JSON1 \
                -DSQLITE_ENABLE_LOAD_EXTENSION \
                -DSQLITE_ENABLE_RTREE \
                -DSQLITE_ENABLE_STAT4 \
                -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
                -DSQLITE_TEMP_STORE3 \
                -DSQLITE_USE_URI \
                -DSQLITE_DEBUG \
                -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
                -DSQLITE_ENABLE_SELECTTRACE \
                -DSQLITE_ENABLE_WHERETRACE \
                -O2"
        ;;
    * )
        echo "Invalid Option: -$OPTARG" 
        echo $usage >&2
        exit 1
      ;;
  esac
done

LIBS="-lm" ./configure --enable-shared --prefix="$PREFIX"

make

sudo make install

By the way the -DSQLITE options does not work.
Changed the name of SQLite3 to avoid confusing and …
$ ldd ./sqlite3_33_0 
        linux-vdso.so.1 (0x00007ffd32981000)
        libreadline.so.5 => /lib/x86_64-linux-gnu/libreadline.so.5 (0x00007efceddc4000)
        libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007efcedda8000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007efcedc59000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007efcedc53000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007efcedc30000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007efceda3e000)
        libtinfo.so.6 => /lib/x86_64-linux-gnu/libtinfo.so.6 (0x00007efceda0c000)
        /lib64/ld-linux-x86-64.so.2 (0x00007efcee057000)

Presto: .selecttrace is back but I am still not able to reproduce the issue.
Here is the last part of the .selecttrace
                    |   '-- 1
                            '-- RAISE abort('Error you are not allowed to change the status')
2/0/55CC014018C8: WhereBegin
1/82/55CC01420BC8: begin processing:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: after name resolution:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: Constant propagation not helpful
1/82/55CC01420BC8: After all FROM-clause analysis:
'-- SELECT (1/55CC01420BC8) selFlags=0xc4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
1/82/55CC01420BC8: WhereBegin
1/82/55CC01420BC8: end processing
'-- SELECT (1/55CC01420BC8) selFlags=0x40c4 nSelectRow=0
    |-- result-set
    |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
    |-- FROM
    |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
    |-- WHERE
    |   '-- EQ fg.af=40000000.n DDL
    |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
    |       '-- NEW(0)
    '-- LIMIT
        '-- 1
2/0/55CC014018C8: end processing
'-- SELECT (2/55CC014018C8) selFlags=0x4400c4 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
                            |   |-- 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')
                            |-- EQ fg.af=40200000.n DDL
                            |   |-- subquery-expr flags=0x42200800
                            |   |   '-- SELECT (1/55CC01420BC8) selFlags=0x40c4 nSelectRow=0
                            |   |       |-- result-set
                            |   |       |   '-- {0:5} pTab=55CC0141C208 fg.af=40800000.n DDL
                            |   |       |-- FROM
                            |   |       |   '-- {0:*} C1 tab='C1' nCol=8 ptr=55CC0141C208 used=21 DDL
                            |   |       |-- WHERE
                            |   |       |   '-- EQ fg.af=40000000.n DDL
                            |   |       |       |-- {0:0} pTab=55CC0141C208 fg.af=40800000.n DDL
                            |   |       |       '-- NEW(0)
                            |   |       '-- LIMIT
                            |   |           '-- 1
                            |   '-- 1
                            '-- RAISE abort('Error you are not allowed to change the status')

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);

but does this for debug builds:

        export SQLITE3_CFLAGS="-fPIC \
—> Thx for spotting my mistake.

Most (all?) C compilers ignore CFLAGS, as they expect build flags on the command line. It's the configure script that captures the value of CFLAGS and modifies the various build control files accordingly.
—> You're correct that the configure script sets the command-line options to compile the code.

A1_INDEX INTEGER,
    A1_CAT VARCHAR(1) DEFAULT 'W',
These columns are defined as the primary key like this:
   CONSTRAINT A1_PK PRIMARY KEY (
        A1_INDEX,
        A1_CAT
    ),
    UNIQUE (
        A1_INDEX,
        A1_CAT
    )
Please note that there shouldn't be a need for the unique statement.

Table C1 the foreign key column is defined as
    C1_INDEX INTEGER,

and the foreign key definition is:
  CONSTRAINT C1_INDEX_FK FOREIGN KEY (
        C1_INDEX
    )
    REFERENCES A1 (
        A1_INDEX
    ),
    UNIQUE (
        C1_INDEX
    )

Result: It fails to build (Error foreign key mismatch).

Executed the test and got this result:
2/0/563136D280B8: end processing
'-- SELECT (2/563136D280B8) selFlags=0x4400c4 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
                            |   |           '-- 128
                            |   '-- 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')
                            |-- NE fg.af=40200000.n DDL
                            |   |-- subquery-expr flags=0x42200800
                            |   |   '-- SELECT (1/563136D2AFF8) selFlags=0x40c4 nSelectRow=0
                            |   |       |-- result-set
                            |   |       |   '-- {0:5} pTab=563136D21208 fg.af=40800000.n DDL
                            |   |       |-- FROM
                            |   |       |   '-- {0:*} C1 tab='C1' nCol=8 ptr=563136D21208 used=22 DDL
                            |   |       |-- WHERE
                            |   |       |   '-- EQ fg.af=40000000.n DDL
                            |   |       |       |-- {0:1} pTab=563136D21208 fg.af=40800000.n DDL
                            |   |       |       '-- NEW(0)
                            |   |       '-- LIMIT
                            |   |           '-- 1
                            |   '-- 4
                            '-- RAISE abort('Error you are not allowed to change the status')
*** Optimizer Start *** (wctrlFlags: 0x1004)
---- Solution nRow=1
0 0.01.00           C1._2                1 f 01241 N 1 cost 0,39,1
*** Optimizer Finished ***
ONEPASS_SINGLE cursors: 3 4
Error: near line 6: no such column: C1_QTY_PRODUCED

Note that the .selecttrace crashes and the .wheretrace shows and error. What?!
Excuse me! The table C1 has a column C1_QTY_PRODUCED. Here is the table:
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
    )
);
Am I overlooking something?

The missing NEW was forcing an error to verify that the C1_110 trigger was fired. I was wrong. You're telling me that the optimizer scans all of the C1 update triggers because I execute a read on table C1 in de A1_100 trigger.

This explains why I was puzzled by the result. I will need some time to ponder about this.

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);