update multiple rows with trigger after insert (sql server)

Question

I have a table orderDetails that contains the products of an order

productId
color
size
quantity

and a table stock

productId
size
color
stock

When a order is completed I use this query to insert the items in the table orderDetails

INSERT INTO orderDetail(orderId, productId, productColor, productSize, productQuantity , cost productName)
    SELECT     
       @orderId, products_translations.id, cart.productColor, cart.productSize, 
       cart.productQuantity, cart.cost, products_translations.name
    FROM cart 
    INNER JOIN products_translations ON cart.productID = products_translations.id
    WHERE     
        (cart.cartId = @cartId) AND 
        (products_translations.language = 1)

Then I have a trigger on table orderDetails:

ALTER TRIGGER [dbo].[scalaProdotti]
   ON [dbo].[orderDetail]
   FOR INSERT
AS

DECLARE @size int
DECLARE @color char(6)
DECLARE @quantity int
DECLARE @product int

BEGIN
    SELECT @size = productSize FROM inserted
    SELECT @color = productColor FROM inserted
    SELECT @quantity = productQuantity FROM inserted
    SELECT @product = productId FROM inserted
    UPDATE stock SET quantity =  quantity - @quantity WHERE size=@size AND color=@color AND product=@product
END

With this trigger I want to decrease the stock, but only the first product is affected, the other quantities remain the same.

What am I missing?

Thank you.

score 6 · Accepted Answer · answered Dec 10 '11 at 15:54

The main point is: you're assuming that the trigger will be called for each row being inserted - this is not the case.

Your trigger will be called once per statement - but that statement can insert mulitple rows at once.

In such a case, the Inserted table inside the trigger will contain multiple rows and your statements:

SELECT @size = productSize FROM inserted
SELECT @color = productColor FROM inserted
SELECT @quantity = productQuantity FROM inserted
SELECT @product = productId FROM inserted

will fail or will happen to select only the first row inserted and disregard the rest of the inserts.

You need to rewrite your trigger to cope with the fact that Inserted can contain multiple inserted rows at the same time

Your code in the trigger should look something like this:

UPDATE stock 
FROM Inserted i
SET 
     stock.quantity = quantity - i.quantity 
WHERE 
     stock.size = i.size 
     AND stock.color = i.color 
     AND stock.product = i.product

update multiple rows with trigger after insert (sql server)

1 Answers1

Linked