MySQL Error 1442 with two triggers and a stored procedure

Question

I have read alot of articles about this error and doesn't seeme to find a solution to my problem.

As much as I understand, there is no infinite loop or recursive trigger calling in my code. But I still keep getting this error(1442). I've read a lot about how triggers act and how easy it is to make a recursive trigger system by accident but I do not understand where the mistake is. Also people have mistakenly used INSERT/UPDATE on the same row that launches the trigger, in my case it is not that way.

How can I solve this problem? If you have any specifying questions be sure to ask me. I'm really struggeling and need help - looking forward to replies

Here are my triggers and procedure: (Just mentioning that I do not use any DELIMITERs because I use a MySQL Workbench and it's not necessary to use DELIMITERs there)

1)Trigger:

BEFORE INSERT ON `dim_content` FOR EACH ROW
BEGIN
    INSERT INTO sequence (increment) VALUE (new.sys_increment);
END

2)Trigger:

BEFORE INSERT ON `sequence` FOR EACH ROW
BEGIN
    DECLARE z INT(15);
    DECLARE w VARCHAR(10);
    SET z = NEW.increment;
    CALL alpha_numeric(z, w);
    UPDATE dim_content SET content_id = w WHERE sys_increment = z;
END

3)Procedure alpha_numeric():

PROCEDURE `alpha_numeric`(INOUT x INT(7), OUT y VARCHAR(10))
BEGIN
    DECLARE y CHAR(7);
    WHILE EXISTS(SELECT * FROM sequence WHERE increment = x) DO
    SET x = x+1;
    END WHILE;
    IF (x < 9999) THEN
    BEGIN
    SET y = CONCAT('A', convert(x, CHAR(4)));
    END;
    /* There are more elif statements but that's not point of the problem */
END;

The practicable order of my logic is following:

INSERT INTO dim_content -> trigger BEFORE INSERT on dim_content -> INSERT INTO sequence -> trigger BEFORE INSERT on sequence -> CALL alpha_numeric() -> trigger BEFORE INSERT on sequence (continues) -> UPDATE dim_content

Answer 1

The error you get:

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

The problem is with your second trigger:

BEFORE INSERT ON `sequence` FOR EACH ROW
BEGIN
    DECLARE z INT(15);
    DECLARE w VARCHAR(10);
    SET z = NEW.increment;
    CALL alpha_numeric(z, w);
    UPDATE dim_content SET content_id = w WHERE sys_increment = z;
END

The last statement updates the dim_content table. But the second trigger is executed because you inserted a record in dim_content table.

This behavior is forbidden by MySQL, hence the error.

The documentation says:

Can triggers access tables?

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.