0

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?

1
Contributor
1
Reply
7
Views
3 Years
Discussion Span
Last Post by minitauros
1

Solved it, I got kinda lost in the bunch of triggers I was creating, and apparently in all the confusion I accidentally changed a trigger to BEFORE delete instead of AFTER delete. My bad!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.