为什么这个触发器不阻止在sqlite中插入行?

Question

以下是我的表格:

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表中插入一行，如果该ID的用户已经超过了他的信用额度或他的信用额度已经过期，但是，当我使用这个fiddle测试它时，触发器没有引发错误。目前，触发器只是检查是否允许的积分数量与添加到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;

Answer 1

您必须为聚合函数SUM()使用COALESCE()，以便当它返回NULL时，因为没有行满足条件，您将获得0，这与第一个子查询的COUNT(*)的结果相当:

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;

Answer 2

从长期经验来看，我想警告你，不要这样做。如果可以使用声明的引用完整性(DRI)，就不要使用触发器，并且除了引用完整性之外，不要使用触发器。特别是，不要对业务规则使用触发器。

如果你不这样做，总有一天你会后悔的。

应该使用事务。例如，编写插入语句来执行以下规则:

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

测试受影响的行，看看它是0还是1，如果没有插入任何内容(意味着不满足条件)，则显示错误。

在我的SQLite应用程序中，我保留了一个命名的"存储过程"的关联数组，因此SQL永远不会出现在应用程序逻辑中。我在数组中按名称查找SQL文本，准备它，并执行它。这样，如果SQL有问题，只有一个地方可以查看。

作为对业务规则使用触发器可能出错的示例，请考虑如果同时插入两个用户，其中一个满足条件，另一个不满足条件，会发生什么情况。您真的希望两行都被拒绝吗?(因为这就是将要发生的事情。)这不是一个用例?好吧，它是:您正在编写关于表的规则，而不是在违反规则时应用程序将做什么。表可以在应用程序之外更新。

这样想:如果你的规则被违反，你的数据仍然是正确的。数据库中没有不一致的内容。所发生的一切就是允许用户超越某些约束。你可以在任何时候标记它:在一段时间内扫一遍牌桌，在游戏开始前检查，等等。但是，如果该表不是通过您精心编写的INSERT语句更新的，那么您总是可以在稍后捕获错误，并将其设置为正确的。