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?
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.
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With