Update table after Insert to another

0

I have a table called Consumption, in this table I have a Quantity column. I have another table called Product, in this table I have an Inventory column.

Whenever a new consumption is entered, one must choose a product and the quantity that was consumed of that product.

I need to make a trigger where every consumption consumed decreases the stock of the chosen product according to the amount reported. Example: Product 1 has 10 in stock. Product Consumption 1, 2 quantities. Update product stock 1 to 8.

I did not reach the conclusion of any code. Thank you.

    
asked by anonymous 21.03.2018 / 18:48

2 answers

3
CREATE or ALTER TRIGGER DiminuirEstoque
ON Consumo
AFTER INSERT
AS
BEGIN
    UPDATE PRODUTO SET ESTOQUE = ESTOQUE - C.QUANTIDADE
    FROM INSERTED C JOIN PRODUTO P ON C.CODIGOPRODUTO = P.CODIGO
END

I think it looks better this way

    
21.03.2018 / 19:19
0

I was able to resolve with the following Trigger

CREATE or ALTER TRIGGER DiminuirEstoque
ON Consumo
AFTER INSERT
AS
BEGIN
    declare @qtdEstoque int
    select @qtdEstoque = Quantidade from Inserted
    declare @codigoProduto int
    select @codigoProduto = CodigoProduto from Inserted
    Update Produto set Estoque = (Select Estoque from Produto where Codigo = @codigoProduto) - @qtdEstoque where Codigo = @codigoProduto
END
    
21.03.2018 / 19:06