6

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.

  • 1
    Can you show some data about the tables in question and tell us exactly what the triggers do and how they are defined? – Brendon Dugan Mar 05 '12 at 16:35
  • @kensou: Can you show us your schema and what the two triggers are supposed to achieve? It probably can be normalized to avoid circular triggers (or all of them). – ypercubeᵀᴹ Mar 05 '12 at 17:22

1 Answers1

11

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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • It may be worthwhile to write BEFORE DELETE triggers to delete records, and AFTER DELETE triggers to reset @deleting variable to NULL. I have modified answer. – Devart Mar 05 '12 at 17:16
  • Why do we need the trigger 3 and 4 ? aren't we resetting the @deleting to NULL just after delete in the BEFORE DELETE triggers? – Kennet Celeste Sep 21 '18 at 13:50
  • Also, could you please describe where the `deleting` should be declared so that it is accessible by all triggers? – Kennet Celeste Sep 21 '18 at 13:51
  • 2
    @KennetCeleste variables that start with `@` in MySQL are global per the session, so `SET @deleting = 1;` is what defines it. `select @deleting` before it is defined will yield `null` – Brian Leishman Sep 24 '18 at 18:53
  • So this approach won't work with remote servers, right? – temo May 08 '19 at 10:57