MYSQL: Can’t update table in stored function/trigger

I have a table called “report”

id|name_group  |ip_element |value         |path           |state
------------------------------------------------------------------
1 |NE          |10.66.16.54|90%           |/dev/map/3-3   |ok
------------------------------------------------------------------
2 |NE          |10.66.16.55|90%           |/dev/map/4-4   |failed
------------------------------------------------------------------
3 |NE          |10.66.16.56|90%           |/dev/map/5-5   |ok
------------------------------------------------------------------
4 |NE          |10.66.16.55|90%           |/dev/map/5-5   |failed

I am trying to create a trigger for in case insert a state with value “failed” for the ip_element “10.66.16.55” and path “/dev/map/4-4”, the state change to “ok”

DELIMITER $$
CREATE TRIGGER UpdateState
AFTER INSERT
ON report
FOR EACH ROW
BEGIN

    UPDATE report SET state = 'Ok'
    WHERE  ip_element = '10.66.16.55' and path = '/dev/map/4-4 '
           

END $$
DELIMITER;

I get the next error:

Can't update table 'report' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Answer

You cannot update the table on which you have a trigger (as the error message states).

Instead, you have to change the data of the insert in the trigger and change the trigger to BEFORE INSERT

DELIMITER $$
CREATE TRIGGER UpdateState
BEFORE INSERT
ON report
FOR EACH ROW
BEGIN

    IF NEW.ip_element = '10.66.16.55' AND  NEW.path = '/dev/map/4-4 ' THEN
        SET NEW.state = 'Ok';
    END IF;

END $$
DELIMITER;

This way any time a value is going to be inserted, you intercept it and change its state if necessary.

Read Mysql Reference for more information.