Sqlite: триггер для ограничения количества значений в строке

У меня есть таблица sqlite, в которой указаны характеристики продукта. Особенности, например. цвет, находятся в логических столбцах. Столбцы имеют значение «1» или «NULL». Некоторые функции являются эксклюзивными. Поскольку структура таблицы фиксирована, я попытался создать триггер, вызывающий исключение, но он не работает. Что я делаю не так?

Пример:

CREATE TABLE productColor(
    productId INTEGER PRIMARY KEY,
    isRed INTEGER,
    isBlue INTEGER,
    isYellow INTEGER,
    isBrown INTEGER)

Товар может быть только одного цвета. Моя идея состояла в том, чтобы подсчитывать столбцы, в которых указано более одного цвета, то есть менее трех значений NULL. Если есть хотя бы одна такая строка, триггер должен вызвать исключение. Вот как выглядит мой триггер:

CREATE TRIGGER onlyOneColor
    BEFORE INSERT ON productColor
    WHEN 
        (SELECT COUNT (*) FROM 
        (SELECT ((CASE WHEN isBrown IS NULL THEN 1 ELSE 0 END) +
        (CASE WHEN isRed IS NULL THEN 1 ELSE 0 END) +
        (CASE WHEN isBlue IS NULL THEN 1 ELSE 0 END) +
        (CASE WHEN isYellow IS NULL THEN 1 ELSE 0 END))
        AS sumOfNulls FROM productColor) WHERE sumOfNulls<3)
    >=1
    BEGIN
    SELECT RAISE(FAIL, 'More then one color specified');
    END;

sqlite database-trigger
person Fab    schedule 17.07.2017    source источник
comment
Какие строки вы хотите проверить? Тот, который нужно вставить, или любые другие?   -  person CL.    schedule 17.07.2017
comment
Вставленного будет достаточно. Я предполагаю, что триггер, настроенный, как описано, проверяет все строки, но я хотел решать одну проблему за раз.   -  person Fab    schedule 17.07.2017

Ответы (1)


arrow_upward
1
arrow_downward

Вам не нужен триггер; это можно сделать с проверочными ограничениями:

CREATE TABLE productColor (
    productId INTEGER PRIMARY KEY,
    isRed     INTEGER  CHECK (isRed    = 1 OR isRed    IS NULL),
    isBlue    INTEGER  CHECK (isBlue   = 1 OR isBlue   IS NULL),
    isYellow  INTEGER  CHECK (isYellow = 1 OR isYellow IS NULL),
    isBrown   INTEGER  CHECK (isBrown  = 1 OR isBrown  IS NULL),
    CHECK (ifnull(isRed,    0) + ifnull(isBlue,  0) +
           ifnull(isYellow, 0) + ifnull(isBrown, 0) <= 1)
);

Если бы вы сохранили все значения как 0 или 1, эти проверки были бы проще. (0 не требуется больше места для хранения, чем NULL.)

Если вам действительно нужен триггер, вы можете поместить инвертированные условия в предложение WHEN. (Обратите внимание, что доступ к значениям в строке, которую нужно вставить, можно получить с помощью NEW.isRed и т. д.)

person CL.    schedule 17.07.2017
comment
Решение с контрольными ограничениями работает отлично, дальше триггером не занимался - person Fab; 19.07.2017