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.
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 $$
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?