在SQL Server触发器中处理多行 [英] Handling multiple rows in SQL Server trigger

问题描述

我们有一个数据库，其中包含一个名为 WarehouseItem 的表，用于保存产品的库存水平。我需要知道该表何时更新，因此我创建了一个触发器来放置此表行的主键。

We have a database with a table called WarehouseItem where product's stock levels are kept. I need to know when ever this table get's updated, so I created a trigger to put the primary key of this table row that got updated; into a separate table (like a queue system).

这是我的触发器：

IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'IC_StockUpdate') > 0)
    DROP TRIGGER [dbo].[IC_StockUpdate]
GO
CREATE TRIGGER [dbo].[IC_StockUpdate] ON [dbo].[WarehouseItem]
AFTER UPDATE
AS
BEGIN

    -- Get Product Id
    DECLARE @StockItemID INT = (SELECT ItemID FROM INSERTED);
    DECLARE @WarehouseID INT = (SELECT WarehouseID FROM INSERTED);

    -- Proceed If This Product Is Syncable
    IF (dbo.IC_CanSyncProduct(@StockItemID) = 1)
    BEGIN

        -- Proceed If This Warehouse Is Syncable
        IF (dbo.IC_CanSyncStock(@WarehouseID) = 1)
        BEGIN

            -- Check If Product Is Synced
            IF ((SELECT COUNT(*) FROM IC_ProductCreateQueue WHERE StockItemID = @StockItemID) > 0)
            BEGIN

                -- Check If Stock Update Queue Entry Already Exists
                IF ((SELECT COUNT(*) FROM IC_StockUpdateQueue WHERE StockItemID = @StockItemID) > 0)
                BEGIN

                    -- Reset [StockUpdate] Queue Entry
                    UPDATE IC_StockUpdateQueue SET Synced = 0
                    WHERE StockItemID = @StockItemID;

                END
                ELSE
                BEGIN

                    -- Insert [StockUpdate] Queue Entry
                    INSERT INTO IC_StockUpdateQueue (StockItemID, Synced) VALUES
                    (@StockItemID, 0);

                END

            END
            ELSE
            BEGIN

                -- Insert [ProductCreate] Queue Entry
                INSERT INTO IC_ProductCreateQueue (StockItemID, Synced) VALUES
                (@StockItemID, 0);

                -- Insert [StockUpdate] Queue Entry
                INSERT INTO IC_StockUpdateQueue (StockItemID, Synced) VALUES
                (@StockItemID, 0);

            END

        END

    END

END
GO

如果 WarehouseItem表中仅更新了一行，则此方法可以很好地工作。
但是，如果此表中更新了多个行，则我的触发器无法处理它：

This works perfectly fine, if only a single row is updated in the "WarehouseItem" table. However, if more than one row is updated in this table, my trigger is failing to handle it:

是否有一种方法可以在批量处理后迭代插入的集合更新事件？还是一个人如何处理触发器中的多行更新？

Is there a way to iterate through the "inserted" collection after a mass update event? Or how does one handle multiple row updates in trigger?

推荐答案

您使用以下命令：

-- Get Product Id
DECLARE @StockItemID INT = (SELECT ItemID FROM INSERTED);
DECLARE @WarehouseID INT = (SELECT WarehouseID FROM INSERTED);

但是如果更新多行（作为样本），则必须使用其他策略。

But if you update multi rows (as your sample) you must use a different strategy.

例如，要声明变量，请在查询中使用JOIN的INSERTED表现在使用变量。

For example, instead to declare a variable, use INSERTED table in JOIN in query where now you use your variable.

IF语句对您的变量起作用，但我想在查询中移动该条件。

IF statement works on your variable but I think to move that condition in query.

尝试以这种方式更改UPDATE查询（最终添加IF条件）：

Try to change you UPDATE query in this way (eventually add condition of IF):

-- Reset [StockUpdate] Queue Entry
UPDATE IC_StockUpdateQueue SET Synced = 0
FROM inserted 
WHERE inserted.itemID = StockItemID;

以此类推。

信息，请添加评论。

这篇关于在SQL Server触发器中处理多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！