Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

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?

like image 282
Latheesan Avatar asked Sep 09 '13 10:09

Latheesan


People also ask

Can I use CTE in trigger?

This means that CTE is valid only to the scope of the query. However, you can write a CTE inside a stored procedure or User Defined Functions (UDFs) or triggers or views. However, you cannot implement CTEs inside indexed views.

Can triggers be nested?

DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

Can we create more than one trigger of same event on same table?

You can create multiple triggers for the same subject table, event, and activation time. The order in which those triggers are activated is the order in which the triggers were created.

Why we use each row in trigger?

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW , then the trigger fires once for each row of the table that is affected by the triggering statement.


2 Answers

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.

like image 194
Joe Taras Avatar answered Oct 24 '22 08:10

Joe Taras


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
like image 40
T I Avatar answered Oct 24 '22 07:10

T I