在SQL Server触发器中处理多行

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).

我们有一个名为WarehouseItem的数据库,其中保留了产品的库存水平。我需要知道这个表什么时候更新,所以我创建了一个触发器来放置更新的这个表行的主键;进入一个单独的表(如队列系统)。

This is my trigger:

这是我的触发器:

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

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:

如果在“WarehouseItem”表中只更新了一行,则此工作完全正常。但是,如果在此表中更新了多行,则我的触发器无法处理它:

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?

有没有办法在批量更新事件后迭代“插入”集合?或者如何在触发器中处理多行更新?

2 个解决方案

#1

You use this:

你用这个:

-- 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.

但是,如果更新多行(作为样本),则必须使用不同的策略。

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

例如,要声明变量,请在查询中使用JOERT表,现在使用变量。

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

IF语句适用于您的变量,但我认为在查询中移动该条件。

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

尝试以这种方式更改UPDATE查询(最终添加IF的条件):

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

And so on.

等等。

For further information please add comment.

如需进一步信息,请添加评论。

#2

You could use a loop to iterate over INSERTED but it may be better to change your scalar variables into a TABLE and INSERT-SELECT from INSERTED where the IDs meet the criteria of the first two IFs

您可以使用循环来迭代INSERTED,但最好将标量变量更改为INSERTED中的TABLE和INSERT-SELECT,其中ID符合前两个IF的条件

DECLARE @inserted TABLE (StockItemID INT, WarehouseID INT)

INSERT INTO @inserted (StockItemID, WarehouseID)
SELECT StockItemID, WarehouseID
FROM INSERTED i
WHERE dbo.IC_CanSyncProduct(i.StockItemID)=1
AND dbo.IC_CanSyncStock(i.WarehouseID)=1

then you can remove the if else upsert logic and use queries that further filter @inserted for the various updates and inserts that are required

然后你可以删除if else upsert逻辑并使用进一步过滤@inserted的查询来获取所需的各种更新和插入

;WITH ResetQueueEntry
(
    SELECT StockItemID
    FROM @inserted i
    WHERE EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
    AND EXISTS(SELECT 1 FROM IC_StockUpdateQueue q WHERE q.StockItemID = i.StockItemID))
)

-- Reset [StockUpdate] Queue Entry
UPDATE IC_StockUpdateQueue 
SET Synced = 0
WHERE StockItemID IN (SELECT StockItemID FROM ResetStockUpdate);

WITH InsertQueueEntry
(
     SELECT StockItemId, 0 Synced
     FROM @inserted
     WHERE EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)       
     AND NOT EXISTS(SELECT 1 FROM IC_StockUpdateQueue q WHERE q.StockItemID = i.StockItemID))
)
-- Insert [StockUpdate] Queue Entry
INSERT INTO IC_StockUpdateQueue (StockItemID, Synced)
SELECT StockItemID, Synced
FROM InsertQueueEntry

WITH CreateProductEntry
(
     SELECT StockItemId, 0 Synced
     FROM @inserted
     WHERE NOT EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
)
-- Insert [ProductCreate] Queue Entry
INSERT INTO IC_ProductCreateQueue (StockItemID, Synced)
SELECT StockItemId, Synced
FROM CreateProductEntry

WITH CreateStockEntry
(
     SELECT StockItemId, 0 Synced
     FROM @inserted
     WHERE NOT EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
)
-- Insert [StockUpdate] Queue Entry
INSERT INTO IC_StockUpdateQueue (StockItemID, Synced)
SELECT StockItemId, Synced
FROM CreateProductEntry