How to avoid Trigger cyclical dependencies in MySQL

Question

I have a little problem using triggers in MySQL.

Suppose we have two tables:

• Tablea
• Tableb

And 2 triggers:

• TriggerA: triggered when deleting on TableA and updating TableB
• TriggerB: fires when deleted on TableB and is deleted in TableA

The problem is that when I delete several rows in TableB, TriggerB starts and deletes some items in table A, then TriggerA starts and tries to update TableB.

This fails because TriggerA tries to update some rows in tableB that are deleted.

How can I avoid these circular dependencies?

None of these two triggers are useless, so I don’t know what I have to do to solve this problem.

Answer 1

Try using a 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