Trigger Problem After Update with Mysql Internal Update

Question

I am creating a Trigger in mysql, Trigger is created but when I update the flag to test mysql returns me this error

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

If anyone can help. Follow Rigger’s sql:

DELIMITER $$

CREATE TRIGGER Data_entrada AFTER UPDATE ON scadabr.aux_Batelada FOR EACH ROW
BEGIN
if OLD.flag_entrada=0 AND NEW.flag_entrada=1 THEN
    UPDATE scadabr.aux_Batelada SET dtEntrada=NOW() where idBatelada = 0;
end if;
if NEW.flag_final=1 then
    INSERT INTO `scadabr`.`Batelada` (`dtEntrada`, `QTCerragem`, `QTKnor`, `QTCinzas`, `QTRest`, `QTPodas`, `QTPalha`, `VLTempMax`, `VLPesoTotal`,  `VLTempRetirada`,`QtSuper`,`QtComplex`) VALUES (NEW.dtEntrada, NEW.QTCerragem, NEW.QTKnor, NEW.QTCinzas, NEW.QTRest, NEW.QTPodas, NEW.QTPalha, NEW.VLTempMax, NEW.VLPesoTotal, NEW.QtSuper, NEW.QtComplex);
end if; 
END$$

DELIMITER ;

Answer 1

Good afternoon.

You cannot perform an update on the same table that is creating the trigger this can make your table enter in loop.

Thus an alternative is to change when the trigger is triggered by AFTER for BEFORE.

Then you can manipulate how the data will look using the NEW.

DELIMITER $$

DROP TRIGGER IF EXISTS Data_entrada;
DELIMITER |
CREATE TRIGGER Data_entrada BEFORE UPDATE ON scadabr.aux_Batelada FOR EACH ROW
BEGIN

if OLD.flag_entrada=0 AND NEW.flag_entrada=1 THEN
    SET NEW.dtEntrada = NOW();
end if;

if NEW.flag_final=1 then
    INSERT INTO `scadabr`.`Batelada` (`dtEntrada`, `QTCerragem`, `QTKnor`, `QTCinzas`, `QTRest`, `QTPodas`, `QTPalha`, `VLTempMax`, `VLPesoTotal`,  `VLTempRetirada`,`QtSuper`,`QtComplex`) VALUES (NEW.dtEntrada, NEW.QTCerragem, NEW.QTKnor, NEW.QTCinzas, NEW.QTRest, NEW.QTPodas, NEW.QTPalha, NEW.VLTempMax, NEW.VLPesoTotal, NEW.QtSuper, NEW.QtComplex);
end if; 

END
|
DELIMITER ;