如何避免MySQL中的循环触发器依赖项 [英] How to avoid circular Trigger dependencies in MySQL

问题描述

我在MySQL中使用触发器存在一些问题.

I have a little probleme using Triggers in MySQL.

假设我们有2个表:

• TableA
• TableB

和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

这篇关于如何避免MySQL中的循环触发器依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！