CODEWITHSUNDEEP
mysql
JCalcines
JCalcines

Reputation: 1286

MySQL Error #1442. How to avoid it

I'm trying to create a hieralchical structure of families, marking the leaf nodes. I have a Trigger to mark the family as a leaf but when I try to save a record the following error happens:

Error Code: 1442. Can't update table 'family' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

My trigger is:

CREATE TRIGGER MARK_LEAF BEFORE INSERT ON FAMILY FOR EACH ROW
BEGIN
    DECLARE V_IS_LEAF  CHAR(1);

    SELECT IS_LEAF INTO V_IS_LEAF FROM FAMILY WHERE ID = NEW.PARENT_ID;

    IF (V_IS_LEAF = 'F') THEN
        UPDATE FAMILY SET IS_LEAF = 'T' WHERE ID = NEW.PARENT_ID;
    END IF;
END

Note: family fields are ID, PARENT_ID, IS_LEAF

Note 2: Notice that I always update the parent_id and my structure is hieralchical so there isn't a loop. Besides, I check if the value is 'F' and I try to change it to 'T', so, if I made a mistake with the parent it only update the same row once.

Is there any way to avoid this error?

Upvotes: 1

Views: 612

Answers (1)

JCalcines
JCalcines

Reputation: 1286

There is no solution because this is a restriction of MySQL. In the documentation they say this is one of their restrictions is :

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

So, I'm afraid that the solution is to put the mark by updating the rows in my programming language

Upvotes: 1

Related Questions