插入后使用触发器更新多行（SQL Server） [英] update multiple rows with trigger after insert (sql server)

问题描述

我有一个表orderDetails，其中包含订单的产品

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

• productId


• 颜色


• 大小


• 数量

• productId
• color
• size
• quantity

和一个表股票

• productId


• size


• 颜色


• 库存

• productId
• size
• color
• stock

订单完成后，我使用此查询将商品插入表格 orderDetails

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)

然后我在表 orderDetails 上有一个触发器：

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.

我缺少什么？

谢谢。

推荐答案

要点是：您假设每个触发器都会被调用插入行-不是这种情况。

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.

在这种情况下，触发器内的 Inserted 表将包含多行和您的语句：

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

这篇关于插入后使用触发器更新多行（SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！