Вот мои таблицы:
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;
Измените
>на>=, потому что это триггер BEFORE INSERT, поэтому он проверяет перед вставкой новой строки.@forpas спасибо - это решило! Знаете ли вы, почему триггер не работает, когда я добавляю
AND expiration >= datetime('now'), чтобы проверить, не истек ли срок действия кредитов. Это скрипка, которую я использовал для проверки.Я не уверен в логике, которую вы хотите применить. У вас есть 2 строки в
creditдля пользователя = 1 с истечением срока действия = «сейчас» - 2 года. Какие вставки вы пытаетесь предотвратить?@forpas Я хочу предотвратить вставку в
users_items, если для этого пользователя недостаточно кредитов, срок действия которых еще не истек.Итак, для пользователя = 1 в вашей скрипке, поскольку срок действия всех кредитов истек 2 года назад, было бы невозможно что-либо вставить в users_tems?
@forpas Точно. Это то, чего я пытаюсь достичь.
Хорошо, проверьте мой ответ.