Subquery returned more than 1 value - My trigger cannot handle more than one row update

Question

I have a trigger on a table called StockItem, which fires on table row update event in the MSSQL.

The trigger seems to work, if only one row (i.e. one product) is updated.

However, when there is more than one row updated, I get the following error:

Msg 512, Level 16, State 1, Procedure IC_ProductUpdate, Line 7

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Here's my trigger:

ALTER TRIGGER [dbo].[IC_ProductUpdate] ON [dbo].[StockItem]
AFTER UPDATE
AS
BEGIN

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

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

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

            -- Check If Any Important Columns Was Updated
            IF (UPDATE(Weight) OR UPDATE(SpareNumber1))
            BEGIN

                -- Check If There Is A [ProductUpdate] Queue Entry Already Exist For This Product
                IF ((SELECT COUNT(*) FROM IC_ProductUpdateQueue WHERE StockItemID = @StockItemID) > 0)
                BEGIN

                    -- Reset [ProductUpdate] Queue Entry
                    UPDATE IC_ProductUpdateQueue SET Synced = 0
                    WHERE StockItemID = @StockItemID

                END
                ELSE
                BEGIN

                    -- Insert [ProductUpdate] Queue Entry
                    INSERT INTO IC_ProductUpdateQueue (StockItemID, Synced) VALUES
                    (@StockItemID, 0)

                END

            END

        END
        ELSE
        BEGIN

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

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

        END

    END

END

What is the best way to handle multi-row update within my trigger?

Answer 1

So, I think I have tracked your logic throughout, and made it set based. I've tried to comment the two sections appropriately, but there is quite a bit going on so feel free to ask me to elaborate on certain sections.

The basis appears to be that only records where dbo.IC_CanSyncProduct(i.ItemID) = 1 are affected then there are 3 branches:

1. Item does not exist in IC_ProductCreateQueue

   --> INSERT into both IC_ProductCreateQueue and IC_ProductUpdateQueue

2. Key fields were updated and the item exists in both IC_ProductCreateQueue and IC_ProductUpdateQueue

   --> UPDATE IC_ProductUpdateQueue

3. Key fields were updated and the item exists in both IC_ProductCreateQueue but not in IC_ProductUpdateQueue

   --> INSERT IC_ProductUpdateQueue

Scenarios 2 and 3 and managed by the MERGE below, scenario 1 is handled by the insert statement.

ALTER TRIGGER [dbo].[IC_ProductUpdate] ON [dbo].[StockItem]
AFTER UPDATE
AS
BEGIN

    -- MERGE ITEMS INTO UPDATE QUEUE WHERE KEY FIELDS WERE UPDATED
    -- AND PRODUCT CAN BE SYNCED AND THE ITEM EXISTS IN THE CREATE QUEUE
    WITH MergeData AS
    (   SELECT  i.ItemID
        FROM    inserted AS i
                INNER JOIN deleted AS d
                    ON d.ItemID = i.ItemID
        WHERE   dbo.IC_CanSyncProduct(i.ItemID) = 1
        AND     (i.SpareNumber1 != d.SpareNumber1 OR i.Weight != d.Weight)
        AND     EXISTS 
                (   SELECT  1 
                    FROM    IC_ProductCreateQueue AS pcq
                    WHERE   pcq.StockItemID = i.ItemID
                )
    )
    MERGE IC_ProductUpdateQueue AS puq
    USING MergeData AS i
        ON i.ItemID = puq.StockItemID
    WHEN MATCHED THEN 
        UPDATE SET Synced = 1
    WHEN NOT MATCHED THEN 
        INSERT (StockItemID, Synced)
        VALUES (i.ItemID, 0);


    -- INSERT INTO BOTH THE UPDATE AND CREATE QUEUES WHERE THE ITEM 
    -- DOES NOT ALREADY EXIST IN THE CREATE QUEUE AND THE PRODUCT
    -- CAN BE SYNCED
    INSERT IC_ProductCreateQueue (StockItemID, Synced)
    OUTPUT inserted.StockItemID, inserted.Synced 
    INTO IC_ProductUpdateQueue (StockItemID, Synced)
    SELECT  i.ItemID, 0
    FROM    inserted AS i
    WHERE   dbo.IC_CanSyncProduct(i.ItemID) = 1
    AND     NOT EXISTS 
            (   SELECT  1 
                FROM    IC_ProductCreateQueue AS pcq
                WHERE   pcq.StockItemID = i.ItemID
            );
END