Handling multiple rows in SQL Server trigger
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).
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:
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?
 
No comments:
Post a Comment