CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Виталик Бушаев
Виталик Бушаев

Reputation: 851

sql trigger on multiple rows

I'm having trouble writing trigger which would update order price.

I have a table that contains order_id and goods_id pairs. Here's the fragment of code that I'm using for deleting. The rest is basically the same.

CREATE TRIGGER [update_price]
       ON [dbo].[Goods-Order]
       AFTER INSERT, DELETE, UPDATE
    AS    
    BEGIN 
        if (exists(select * from deleted))
            UPDATE dbo.Order set order_price -= (input_amount * (select goods_price from dbo.Goods where dbo.Goods.goods_id = deleted.goods_id)) from deleted where dbo.Orde.order_id = deleted.order_id

The problem is that if there are multiple rows with the same order_id in Order-Goods table, update statement will only process one of them.

I've seen a bunch of similar examples on stack overflow, however, they are a bit different and do not apply for my situation.

I understand that there is a solution iterating over all rows in deleted table using cursors but I would like to avoid that approach. Is it possible?

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use JOINs for bringing the tables together:

UPDATE o 
    SET order_price -= d.input_amount * g.goods_price 
    FROM dbo.Order o JOIN
         deleted d
         ON o.order_id = d.order_id JOIN
         dbo.Goods g
         ON g.goods_id = d.goods_id;

EDIT:

If an Order can have multiple rows with the same good, then aggregate before joining:

UPDATE o 
    SET order_price -= d.input_amount * g.goods_price 
    FROM dbo.Order o JOIN
         (SELECT d.order_id, d.goods_id, SUM(d.input_amount) as input_amount
          FROM deleted d
          GROUP BY d.order_id, d.goods_id
         ) d
         ON o.order_id = d.order_id JOIN
         dbo.Goods g
         ON g.goods_id = d.goods_id;

Upvotes: 2

Related Questions