问题描述
我在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中的循环触发器依赖项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!