How to avoid circular Trigger dependencies in MySQL

Question

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.

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
– Brendon Dugan, Commented Mar 5, 2012 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ᵀᴹ
– ypercubeᵀᴹ, Commented Mar 5, 2012 at 17:22

Devart · Accepted Answer · 2012-03-05 17:15:59Z

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

edited Mar 5, 2012 at 17:15

answered Mar 5, 2012 at 16:45

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Devart Over a year ago

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.

Kennet Celeste Over a year ago

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 Over a year ago

Also, could you please describe where the deleting should be declared so that it is accessible by all triggers?

Brian Leishman Over a year ago

@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

temo Over a year ago

So this approach won't work with remote servers, right?

Collectives™ on Stack Overflow

How to avoid circular Trigger dependencies in MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related