CODEWITHSUNDEEP
sqlsql-server
BPC
BPC

Reputation: 13

How can i use information from another table in a trigger?

I tried this, but it didn't work. I wanted to use the "valor" row to make this trigger.

CREATE TRIGGER valor_total ON Detalhe_Venda
    AFTER INSERT
    AS
        DECLARE @vendaid INT
        DECLARE @produtoid INT
        DECLARE @qtd_produtos INT
        DECLARE @valor FLOAT

        SELECT @vendaid = venda_id, @produtoid = produto_id, @qtd_produtos = quantidadeprod
        FROM INSERTED

        SELECT @valor = valor
        FROM Produtos
        WHERE id = @produtoid

        UPDATE Venda 
        SET valortotal = valortotal + (@valor * @qtd_produtos)
        WHERE id = @vendaid

Upvotes: 0

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You are misunderstanding a lot of things about triggers in SQL Server. The most important is that INSERTED is a set of rows, not a single row.

In fact, you can simplify your whole trigger to a single query:

CREATE TRIGGER valor_total ON Detalhe_Venda
    AFTER INSERT
AS
BEGIN
        UPDATE v 
            SET valortotal = v.valortotal + (p.valor * i.quantidadeprod)
            FROM Venda v JOIN
                 Inserted i
                 ON v.id = i.venda_id JOIN
                 Produtos p
                 ON p.id = i.produtoid;
END;

EDIT:

SMore makes a good point. Aggregation is probably desirable before the join:

CREATE TRIGGER valor_total ON Detalhe_Venda
    AFTER INSERT
AS
BEGIN
        UPDATE v 
            SET valortotal = v.valortotal + ip.valor
            FROM Venda v JOIN
                 (SELECT i.venda_id,
                         SUM(p.valor * i.quantidadeprod) as valor
                  FROM Inserted i JOIN
                       Produtos p
                       ON p.id = i.produtoid
                  GROUP BY i.venda_id
                 ) ip
                 ON v.id = ip.venda_id
END;

Upvotes: 1

Related Questions