Error 1415 in MySQl when creating a trigger

1

Good afternoon, I am trying to create an alert trigger for lack of products, which throws me the error:

  

Not allowed to return to result set from a trigger.

This is the Query of the trigger that I'm trying to create

delimiter $
create trigger alerta
after update
on producto
for each row
begin
    declare _cod_prod int;
    declare _cantidad_actual int;
    declare _cantidad_anterior int;
    declare _cantidad_alerta int;
    set _cod_prod=(select cod_producto from inserted);
    set _cantidad_actual=(select cantidad from inserted where cod_producto=_cod_prod);
    set _cantidad_anterior=(select cantidad_anterior from inserted);
    set _cantidad_alerta=1;
    if(_cantidad_actual<=_cantidad_alerta) then
    begin
        update producto set cantidad=_cantidad_anterior where cod_producto=_cod_prod;
        select * from producto;
    end;
    else
    begin
        update producto set cantidad_anterior=_cantidad_actual where cod_producto=_cod_prod;
        select * from producto;
    end;
    end if;
end $

delimiter ;
    
asked by Esteban Lopez 30.12.2017 в 21:53
source

1 answer

1

The error is quite clear, you can not return a result set in a trigger and that is what you are doing with select * from producto; . Remove those two selects and you should solve this problem.

On the other hand if the idea of returning the records of producto is for some kind of follow-up, what you could do is insert them in a table copy of the original and then verify them.

    
answered by 31.12.2017 в 15:23