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

Question

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

Navigation

#1 by (2 votes)

3

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:

DELIMITER //
CREATE TRIGGER tr_updStockAnular After update on tblingreso for each row 
begin
update tblarticulo SET stock = stock - new.tbldetalleingreso.cantidad
where tblarticulo.idarticulo=new.tbldetalleingreso.idarticulo;
end
//
DELIMITER ;

mysql trigger

asked by Interes Ciencia 11.12.2017 в 07:31

source

1 answer

Create named files that include rare characters Create variables in Razor and then manipulate them in Jquery?

score 2 · Accepted Answer

The 2 problems I see with the trigger are:

new.tbldetalleingreso.xxx : 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 :

DELIMITER //
CREATE TRIGGER tr_updStockAnular after update on tblingreso for each row 
begin
  update tblarticulo a
    join tbldetalleingreso di
      on di.idarticulo = a.idarticulo
     and di.idingreso = new.idingreso
     set a.stock = a.stock - di.cantidad;
end;
//
DELIMITER ;