Handling multiple rows in SQL Server trigger

Question

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:

enter image description here

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?

Following provides good explanation to resolve the issue,mssqltips.com/sqlservertip/2342/… I used the following conditions to capture insert, delete and updates SELECT @ DELCOUNT = COUNT() FROM DELETED SELECT @ INSCOUNT = COUNT() FROM INSERTED IF @ DELCOUNT = 0 -- INSERT , ELSE IF @INSCOUNT = 0 -- DELETE etc — Buminda
– Buminda, Commented Jun 1, 2016 at 23:23

Joe Taras · Accepted Answer · 2013-09-09 10:28:26Z

5

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.

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

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;

And so on.

For further information please add comment.

answered Sep 9, 2013 at 10:28

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Latheesan Over a year ago

I think I understand where you are coming from with this. Thanks for this tip. I think this is what I needed.

Joe Taras Over a year ago

@LatheesanKanes: Ok, if you want, I'm here ;)

Hitesh Over a year ago

This isn't working for me, i tried join with INSERTED table, but it still gives me the same error i.e. subquery returned more than one row. I am trying to update multiple rows in one query using statement like "UPDATE Table1 SET col1 = col1", can you please help me out with this.?

Joe Taras Over a year ago

@HiteshMistry: My answer was related on this question. Please post your context, so I can help you.

Tab Alleman Over a year ago

@Latheesan why have you not accepted this as the answer?

T I · Accepted Answer · 2013-09-11 17:42:58Z

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

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

;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

Omid Mehdizadeh · Accepted Answer · 2018-12-04 16:40:01Z

in case of the trigger is for INSERT, UPDATE this code will exit the trigger IF Records are being updated AND more than one record is being afftected:

IF (SELECT COUNT(*) FROM Deleted) > 1
  BEGIN
     Return
  END

But if you wish to examin every record in the INSERTED recordset you can use this method:

   DECLARE rstAST CURSOR FOR
   SELECT ins.TaskActionId,
          _Task.CustomerId,
          _AST.ASTQRId,
          ins.ExistingQRcode,
          ins.NewQRcode
          FROM Inserted ins INNER JOIN
               dbo.cdn_AST _AST ON ins.ASTId = _AST.ASTId INNER JOIN
               dbo.tsk_Task _Task ON ins.TaskId = _Task.TaskId

    OPEN rstAST
    FETCH NEXT FROM rstAST INTO @TaskActionId, @TaskCustomerId, @ASTQRId, @ExistingQRcode, @NewQRcode
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --use CONTINUE to skip next record or let it traverse the loop

      FETCH NEXT FROM rstAST INTO @TaskActionId, @TaskCustomerId, @ASTQRId, @ExistingQRcode, @NewQRcode
    END
    CLOSE rstAST
    DEALLOCATE rstAST

Collectives™ on Stack Overflow

Handling multiple rows in SQL Server trigger

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related