SQLite Forum

Hmm. Here is the complete code

CREATE TRIGGER IF NOT EXISTS DCS_UPDATE_URI BEFORE UPDATE OF DCS_URI ON DEVICE_COM_SETTINGS
WHEN FCS(23)
--WHEN ((SELECT F_FLAG FROM FLAGS WHERE F_NAME == 'SIGNAL_FLAG_03') == 2)
BEGIN

--    SELECT RAISE (ABORT, 'Table DEVICE_COM_SETTINGS ((INSERT MASTER / STATND-ALONE): ERROR-96: The URI is malformed')
--    WHERE (NEW.DDS_URI REGEXP '^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}');

    DELETE FROM _INET_;
    INSERT INTO _INET_
        WITH RECURSIVE SPLIT(IP_ADDRESS, LAST_OCTET, REMAINDER) AS (
            VALUES ('', '', NEW.DCS_URI)
            UNION ALL
            SELECT CASE
                WHEN LAST_OCTET == '.'
                THEN
                    SUBSTR(REMAINDER, 1, 1)
                ELSE
                    IP_ADDRESS || SUBSTR(REMAINDER, 1, 1)
                END,
                SUBSTR(REMAINDER, 1, 1),
                SUBSTR(REMAINDER, 2)
      FROM SPLIT
      WHERE REMAINDER <> ''
    )
    SELECT
           REPLACE(IP_ADDRESS, '.','') AS 'OCTET'
    FROM
           SPLIT
    WHERE
           LAST_OCTET == '.' OR REMAINDER =='' ;

    -- Debug
--    INSERT INTO _Variables (NAME, VALUE)      
--    VALUES
--      ('OCTET_1', (SELECT OCTET FROM _INET_ WHERE ROWID == 1)),
--      ('OCTET_2', (SELECT OCTET FROM _INET_ WHERE ROWID == 2)),
--      ('OCTET_3', (SELECT OCTET FROM _INET_ WHERE ROWID == 3)),
--      ('OCTET_4', (SELECT OCTET FROM _INET_ WHERE ROWID == 4));

    SELECT CASE
        WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 1) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 1) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-07: The first octet of the URI is invalid')
        WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 2) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 2) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-08: The second octet of the URI is invalid')
        WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 3) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 3) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-09: The third octet of the URI is invalid')
        WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 4) < 1) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 4) > 253)) AND ((SELECT SUM(OCTET) FROM _INET_) NOT IN (0, 1020))
            THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-10: The URI is invalid')
    END;

    -- Prevent and endless recursive loop
--    UPDATE FLAGS SET
--      F_FLAG = 1
--      WHERE F_NAME == 'SIGNAL_FLAG_03';

    -- Update the URI
    UPDATE DEVICE_COM_SETTINGS SET
        DCS_URI = (SELECT OCTET FROM _INET_ WHERE ROWID == 1)
        WHERE DCS_PK_SERIAL_NUMBER == NEW.DCS_PK_SERIAL_NUMBER;

    -- CLEAR the flag   
--    UPDATE FLAGS SET
--      F_FLAG = 2
--      WHERE F_NAME == 'SIGNAL_FLAG_03';
      SELECT CF(23);

END;