Почему этот триггер не предотвращает вставку строк в sqlite?

avatar
knowledge_seeker
8 августа 2021 в 18:52
118
2
2

Вот мои таблицы:

CREATE TABLE users(
    user_id INT,
    channel_id INT NOT NULL UNIQUE,
    PRIMARY KEY (user_id)
);

CREATE TABLE credits(
    user_id INT,
    number_of_items INT CHECK(number_of_items > 0),
    expiration DATETIME,
    PRIMARY KEY (user_id, number_of_items, expiration),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

CREATE TABLE users_items(
    user_id INT,
    item_id INT,
    PRIMARY KEY (user_id, item_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

а вот триггер, который я реализовал:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >
            (SELECT sum(number_of_items) FROM credits WHERE user_id = NEW.user_id)
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;

Я хочу, чтобы этот триггер предотвращал вставку строки в таблицу users_items, если пользователь с этим идентификатором уже превысил свой кредитный лимит или его кредиты истекли, однако, когда я тестировал его, используя это триггер не вызывает ошибку. В настоящее время триггер просто проверяет допустимое количество кредитов на количество элементов, добавленных к users_items, и не проверяет дату, но я планирую проверить дату, добавив что-то вроде AND expiration >= datetime('now') в триггер, чтобы он выглядел как это:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
            (SELECT sum(number_of_items) FROM credits WHERE user_id = NEW.user_id AND expiration >= datetime('now'))
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;
Источник
forpas
8 августа 2021 в 20:51
2

Измените > на >=, потому что это триггер BEFORE INSERT, поэтому он проверяет перед вставкой новой строки.

knowledge_seeker
8 августа 2021 в 22:23
0

@forpas спасибо - это решило! Знаете ли вы, почему триггер не работает, когда я добавляю AND expiration >= datetime('now'), чтобы проверить, не истек ли срок действия кредитов. Это скрипка, которую я использовал для проверки.

forpas
9 августа 2021 в 07:30
0

Я не уверен в логике, которую вы хотите применить. У вас есть 2 строки в credit для пользователя = 1 с истечением срока действия = «сейчас» - 2 года. Какие вставки вы пытаетесь предотвратить?

knowledge_seeker
9 августа 2021 в 14:06
0

@forpas Я хочу предотвратить вставку в users_items, если для этого пользователя недостаточно кредитов, срок действия которых еще не истек.

forpas
9 августа 2021 в 14:17
0

Итак, для пользователя = 1 в вашей скрипке, поскольку срок действия всех кредитов истек 2 года назад, было бы невозможно что-либо вставить в users_tems?

knowledge_seeker
9 августа 2021 в 14:23
0

@forpas Точно. Это то, чего я пытаюсь достичь.

forpas
9 августа 2021 в 14:26
0

Хорошо, проверьте мой ответ.

Ответы (2)

avatar
forpas
9 августа 2021 в 14:26
1

Вы должны использовать COALESCE() для агрегатной функции SUM(), чтобы когда она возвращала NULL из-за отсутствия строк, удовлетворяющих условиям, вместо этого вы получали 0, и это сравнимо с результатом COUNT(*) 1-й подзапрос:

CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
            (SELECT COALESCE(SUM(number_of_items), 0) FROM credits WHERE user_id = NEW.user_id AND expiration >= datetime('now'))
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;

См. демонстрацию.

avatar
James K. Lowden
8 августа 2021 в 21:21
1

Основываясь на многолетнем опыте, хочу предупредить вас, не делайте этого. Не используйте триггеры, если вместо этого можно использовать декларированную ссылочную целостность (DRI), и не используйте триггеры ни для чего, кроме ссылочной целостности. В частности, не используйте триггеры для бизнес-правил.

Если вы не будете следовать этому правилу, однажды вы пожалеете о нем.

Вместо этого используйте транзакцию. Например, напишите оператор вставки, чтобы применить правило:

.
insert into user_items
select ( ... values ... )
where [user has not surpassed his credit limit and his credits have not expired]

Проверить затронутые строки, чтобы увидеть, являются ли они нулем или единицей, и отобразить ошибку, если ничего не было вставлено (это означает, что критерии не были удовлетворены).

В своих приложениях SQLite я сохраняю ассоциативный массив именованных «хранимых процедур», поэтому SQL никогда не появляется в логике приложения. Я ищу текст SQL по имени в массиве, подготавливаю его и выполняю. Таким образом, если возникнет проблема с SQL, искать можно будет только в одном месте.

.

В качестве примера того, что может пойти не так, если вы используете триггеры для бизнес-правил, рассмотрим, что произойдет, если два пользователя будут вставлены одновременно, один из которых соответствует критериям, а другой — нет. Вы действительно хотите, чтобы обе строки были отклонены? (Потому что это то, что произойдет.) Это не вариант использования? Так и есть: вы пишете правило о таблице, а не о том, что будет делать приложение, если правило будет нарушено. Таблицы можно обновлять вне приложений.

Подумайте об этом так: если ваше правило нарушается, ваши данные по-прежнему верны. Ничего противоречащего в базе данных нет. Все, что произошло, это то, что пользователю было разрешено превысить некоторые ограничения. Вы можете отметить это в любое время: периодическая проверка стола, проверка перед началом игры, что угодно. IOW, если таблица каким-либо образом обновляется средствами, отличными от вашего тщательно написанного оператора INSERT, вы всегда можете поймать ошибку позже и все исправить.

knowledge_seeker
8 августа 2021 в 22:14
0

Что делать, если я не собираюсь обновлять таблицу, кроме как через это конкретное приложение?

James K. Lowden
9 августа 2021 в 20:39
1

Это часть того, о чем я вас предупреждаю. Триггер применяется к таблице, а не к приложению. Вы можете намереваться, чтобы он никогда не обновлялся, кроме как через приложение, но создаваемое вами правило выходит за эти границы. Тем самым вы накладываете ненужное ограничение. Правило принадлежит приложению и лучше всего применяется приложением. Это верно для любого правила, которое, как это, не влияет на внутреннюю непротиворечивость базы данных.

knowledge_seeker
9 августа 2021 в 22:55
0

Я понимаю, вместо этого я буду применять правило из приложения. Спасибо за совет!