对于跨表完整性约束，数据库触发器是否安全?

2022-01-01 00:00:00 sql transactions mysql triggers

我建议使用触发器检查交叉表完整性约束来回答这个问题.已经在评论中提示可能会出现问题:

I suggested using a trigger to check a cross table integrity constraint answering this question. It has been suggested in the comments that it may cause problems:

执行跨行检查的触发器很少适用于大多数数据库......因为它们无法从其他事务中读取未提交的行

Triggers to do cross row checks rarely work on most databases ... because they cannot read uncommitted rows from other transactions

不过，我还没有找到任何支持该声明的来源.官方 文档不要提任何东西.我发现的其他问题在此处介绍了 - 它主要批评潜在的隐藏复杂性，因为第一眼看不到触发器.但即使是评分最高的答案也承认它们用于完整性问题.

I haven't found any source supporting the claim, though. Official documentation doesn't mention anything. Others issues I found are covered here on SO - it mostly criticizes potential hidden complexity as triggers are not visible on first sight. But even the highest rated answer admits their usage for integrity issues.

所以我的问题是:数据库触发器对于交叉表完整性约束是否安全?具体来说，下面的解决方案是否有效?

So my question is: Are database triggers safe for cross table integrity constraints? Specifically, would the solution below work?

总结原始问题.我们有桌子

To summarize the original question. We have tables

  • 玩家 - 玩家 ID、玩家姓名
  • 投注 - BetID、BetName
  • plays_in - BetID、玩家 ID

约束条件是 BetName 和 PlayerID 的组合应该是唯一的.建议触发器的定义:

The constraint is that the combination of BetName and PlayerID should be unique. Definition of the suggested trigger:

CREATE TRIGGER check_bet_name BEFORE INSERT ON plays_in 
  FOR EACH ROW BEGIN
      DECLARE bet_exists INT DEFAULT 0;
      DECLARE msg VARCHAR(255);

      SELECT 1 INTO bet_exists 
        FROM Bet AS b1
        WHERE b1.BetID = NEW.BetID
          AND EXISTS (SELECT * 
            FROM plays_in AS p JOIN Bet AS b2 USING (BetID)
            WHERE p.PlayerID = NEW.PlayerID AND b2.BetName = b1.BetName
          )
        LIMIT 1;

      IF bet_exists THEN
        SET msg = "Bet name already exists...";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
      END IF;
  END//

推荐答案

答案是触发器不安全.

事实证明，触发器确实没有看到其他事务中完成的未提交更改并且没有错误地通过.可以这样演示

It turns out that the trigger really doesn't see uncommited changes done in other transactions and passes without an error. It can be demonstrated like this

交易 1:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,1); -- query A

交易 2:

START TRANSACTION;
INSERT INTO plays_in (BetID, PlayerID) VALUES (1,2); -- query B; in conflict with A, but passses

两笔交易:

COMMIT;

现在 plays_in 将包含两个插入的记录，即使 A 和 B 在单个事务中执行，触发器也会抛出错误.

Now plays_in will contains both inserted records even though if A and B were executed in a single transaction, the trigger would throw an error.

可以在这里

相关文章