Problem with Trigger After Update with Internal Update in Mysql

Question

Problem with Trigger After Update with Internal Update in Mysql

Navigation

#1 by (0 votes)

0

I'm creating a trigger in mysql, the trigger is created but when I go to update the flag to test mysql it 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. Here is the trigger 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 ;

mysql sql-update trigger

asked by anonymous 25.07.2018 / 22:48

1 answer

Hot Deploy JSP on JBOSS Detect embedded source in pdf

score 0 · Accepted Answer

Good afternoon.

You can not perform an update on the same table that is creating the trigger . This can cause your table to go into loop .

So an alternative is to change when trigger is triggered from AFTER to BEFORE .

Then you can manipulate how the data will be stored using 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 ;