mysql 触发器用循环,如何避免MySQL中的循环触发器依赖项

I have a little probleme using Triggers in MySQL.

Suppose we have 2 tables:

TableA

TableB

And 2 Triggers:

TriggerA: fires when deleting on TableA and updates TableB

TriggerB: fires when deleting on TableB and deletes in TableA

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.

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.

解决方案

Try to use variable.

First trigger:

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

Second trigger:

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

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