Update table after insert into another

Asked

Viewed 460 times

0

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

Where a new consumption is introduced, a product and the quantity consumed of that product shall be chosen.

I need to make a Rigger where each consumption made decreases the stock of the product chosen according to the quantity informed. Example: Product 1 has 10 in stock. Product Consumption 1, 2 quantities. Update Product Stock 1 to 8.

inserir a descrição da imagem aqui

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

c# sql-server
  • 1

    What data are you working with? Mysql?

  • 1

    Search for Trigger

  • I am using Sql Server

  • 1

    one trigger does not guarantee that the value will even be updated, it seems to me a transactional process, already thought to update everything within a transaction to ensure?

  • Which Server are you working with? C#?

  • I never saw transaction

  • I am working with C#, but I need this in Sql Server

  • Either you work with Trigger, or you implement the solution in C#, or you have no alternative.

  • right in this situation would be Rigger, maaaaaaas, makes an expensive stock table... greatly simplifies

  • table: movimento_estoque ... produto | valor | quantidade | saldo_anterior | saldo_atual in this table only makes Inserts, no update. So you keep all drive history;

  • I managed to solve with Trigger, I will post the code

  • @Ricardopunctual, I think by default triggers have an implicit transaction. If there is an error in the update, Insert would also not be executed

  • Interesting @Diegorafaelsouza I will give a search, thanks

Show 8 more comments

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’s better this way

0

I was able to solve it 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

Browser other questions tagged c# sql-server

You are not signed in. Login or sign up in order to post.