MYSQL How do I make a trigger to update stock after canceling an invoice?


I want that after canceling a record in the tblingreso table the corresponding stock in the articles table is updated (the stock is subtracted):

 UPDATE tblingreso SET estado='Anulado' WHERE idingreso='1';

I have my table tblingreso with the fields:

 idIngreso   int(11)
 idProveedor int(11)
 total  decimal(14,4)
 estado varchar(20)

With data it would be like this:

The detail table tbldetalleingreso:

 iddetalleingreso int(11)
 idingreso int(11)
 idarticulo int(11)
 cantidad int(11)
 preciocompra decimal(14,4)

With data the tbldetalleingreso:

and the table tblarticle:

PS: I tried the following and I could not create the trigger:

CREATE TRIGGER tr_updStockAnular After update on tblingreso for each row 
update tblarticulo SET stock = stock - new.tbldetalleingreso.cantidad
where tblarticulo.idarticulo=new.tbldetalleingreso.idarticulo;
asked by Interes Ciencia 11.12.2017 в 07:31

1 answer


The 2 problems I see with the trigger are:

  • : You can not use this expression to access the information in the tbldetalleingreso table. Using the keyword new , you only have access to the columns of the table tblingreso .
  • You do not have a semicolon after end : end;
  • To be able to update the stock in the tblarticulo table while using the information in the tbldetalleingreso table, you need to make a join in UPDATE :

    CREATE TRIGGER tr_updStockAnular after update on tblingreso for each row 
      update tblarticulo a
        join tbldetalleingreso di
          on di.idarticulo = a.idarticulo
         and di.idingreso = new.idingreso
         set a.stock = a.stock - di.cantidad;
    answered by 11.12.2017 / 14:41