For some reason my triggers are recursing, while I think they shouldn't. I'll simplify the examples for the sake of you not having to read through all the code.
Trigger 1:
CREATE TRIGGER table_1_after_delete AFTER DELETE ON table_1
FOR EACH ROW
BEGIN
DELETE FROM table2
WHERE table2.table_1_key = OLD.table_1_key;
END
$$
Trigger 2:
CREATE TRIGGER table_2_after_delete AFTER DELETE ON table_2
FOR EACH ROW
BEGIN
-- Only execute the UPDATE query if a record still exists in table_1, from which we have
-- deleted a record, triggering this function and targeting back table_1 (recursion).
CASE
WHEN
(SELECT table_1.table_1_key
FROM table_1
WHERE table_1.table_1_key = OLD.table_1_key)
IS NOT NULL
THEN
UPDATE table_1
SET table_1.var1 = NULL,
table_1.var2 = NULL
WHERE table_1.table_1_key = OLD.table_1_key;
ELSE
BEGIN
END;
END CASE;
END;
The problem is: I think that trigger 2 should recognize that the record from table_1 has been deleted, preventing the UPDATE query from being executed. However, it does not recognize this; the UPDATE query IS still being executed, and an error is being thrown: *Can't update table 'table_1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. *
Does anyone know if this is some kind of MySQL restriction? Or might it be expected/intended behaviour? Any workarounds for this?