如何避免 MySQL 中的循环触发器依赖

2022-01-01 00:00:00 circular-dependency mysql triggers

我在 MySQL 中使用触发器时遇到了一个小问题.

I have a little probleme using Triggers in MySQL.

假设我们有 2 个表:

Suppose we have 2 tables:

  • 表A
  • 表B

和 2 个触发器:

  • TriggerA:在 TableA 上删除并更新 TableB 时触发
  • TriggerB:在 TableB 上删除和在 TableA 中删除时触发

问题是，当我删除 TableB 中的某些行时，TriggerB 触发并删除 TableA 中的某些元素，然后 TriggerA 触发并尝试更新 TableB.

The problem is that when I delete some rows in TableB, TriggerB fires and deletes some elements in TableA, then TriggerA fires and tries to update TableB.

它失败是因为 TriggerA 尝试更新 TableB 中正在被删除的一些行.

It fails because TriggerA tries to update some rows in TableB that are being deleted.

如何避免这种循环依赖?

How can I avoid this circular dependencies?

这两个触发器都没有用，所以我不知道我该怎么做才能解决这个问题.

None of those two Triggers are useless, so I don't know what am I supposed to do to solve this.

推荐答案

尝试使用变量.

第一次触发:

CREATE TRIGGER trigger1
  BEFORE DELETE
  ON table1
  FOR EACH ROW
BEGIN
  IF @deleting IS NULL THEN
    SET @deleting = 1;
    DELETE FROM table2 WHERE id = OLD.id;
    SET @deleting = NULL;
  END IF;
END

第二次触发:

CREATE TRIGGER trigger2
  BEFORE DELETE
  ON table2
  FOR EACH ROW
BEGIN
  IF @deleting IS NULL THEN
    SET @deleting = 1;
    DELETE FROM table1 WHERE id = OLD.id;
    SET @deleting = NULL;
  END IF;
END

以及其他 AFTER DELETE 触发器:

And additional AFTER DELETE triggers:

CREATE TRIGGER trigger3
  AFTER DELETE
  ON table1
  FOR EACH ROW
BEGIN
  SET @deleting = NULL;
END

CREATE TRIGGER trigger4
  AFTER DELETE
  ON table2
  FOR EACH ROW
BEGIN
  SET @deleting = NULL;
END

相关文章