Update table after Insert to another

Question

Update table after Insert to another

Navigation

#1 by (3 votes)
#2 by (0 votes)

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.

sql-server c#

asked by anonymous 21.03.2018 / 18:48

2 answers

Updating multiple lines and fields of Mysql Convert String to double in Java

score 3 · Answer 1

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

score 0 · Answer 2

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