Trigger to handle Multi Insert Statements-CodePudding

I have two table Stocks and Purchases

CREATE TABLE Stocks
(
  id int PRIMARY KEY IDENTITY(1,1),
  itemId int NOT NULL,
  qty int  NOT NULL,
  status NVarChar(50) NOT NULL,
)


CREATE TABLE Purchases
(
  id int PRIMARY KEY IDENTITY(1,1),
  itemId int NOT NULL,
  suppId int NOT NULL,
  qty int  NOT NULL,
  Date NVarChar(50) NOT NULL,
  status NVarChar(50) NOT NULL,
)

Here is what I want do:

ON Inserting Multiple Records into Purchases table, I want Create a Trigger that Iterates over each Record Inserted and Check itemId FROM the Stocks Table

UPDATE TheQuantity(qty in this case) of each itemId found AND INSERT into the Stocks Table if itemId isn't found

I have tried several ways, in fact I have been struggling with it since yesterday.

this Post was the closest one I have seen but could figure out what is going on, seems like it's only Updating already existing records

Here is what I have tried so far, I meanthe closest I came

CREATE trigger [dbo].[trPurchaseInsert] on [dbo].[Purchases] FOR 
  INSERT
 AS
DECLARE @id int;
DECLARE @itemId int;
DECLARE @status NVarChar(50) = 'available';

    SELECT @itemId=i.ItemId FROM INSERTED i
 IF EXISTS(SELECT * FROM Stocks WHERE itemId=@itemId)
 BEGIN
      SET NOCOUNT ON;

     DECLARE @itemIdUpdate int;
     DECLARE @qty int;

    SELECT @qty=[quantity], @itemIdUpdate=[itemId] FROM INSERTED i 
    
    UPDATE Stocks SET qty=qty @qty WHERE itemId=@itemIdUpdate
  END
 ELSE
 BEGIN
    SET NOCOUNT ON;
    INSERT INTO Stocks SELECT [itemId], [id], [quantity], @status 
    FROM INSERTED i
 END

this works fine in a single Insert but doesn't work when multiple records are inserted into the Purchase Table at once

The above Trigger Updates the first itemId only and doesn't update the rest or even insert new ones if one itemid is found

The Goal here is to Update in stock items if itemid is found and Insert if itemId isn't found

For Further Details see this SQL Fiddle. It contains Tables & what I have tried with commented details

I have seen several comments advising to use set base operations with joins but couldn't figure a direction

How can I get it to work?

CodePudding user response：

Your trigger is fatally flawed. It does not take into account multiple rows being inserted, it also doesn't deal with updates and deletes.

Instead you should use this:

Note how inserted and deleted are both used. deleted.qty is subtracted.
The whole thing is grouped up by itemId and then the difference is applied to Stocks.
Since you also want to insert, you need to use MERGE. Alternatively, you can have separate joined UPDATE and INSERT...WHERE NOT EXISTS statements.

CREATE OR ALTER TRIGGER dbo.trPurchaseInsert
ON dbo.Purchases
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

MERGE Stocks s
USING (
    SELECT
      diff.ItemId,
      qty = SUM(diff.qty)
    FROM (
        SELECT
          i.ItemId,
          i.qty
        FROM inserted i
        UNION ALL
        SELECT
          d.ItemId,
          -d.qty
        FROM deleted d
    ) diff
    GROUP BY
      diff.ItemId
) diff
ON diff.ItemId = s.ItemId
WHEN NOT MATCHED THEN
  INSERT (itemId, qty, status)
  VALUES (diff.itemId, diff.qty, 'available')
WHEN MATCHED THEN
  UPDATE SET
    qty  = diff.qty;

I must say, I would not implement this with triggers at all.

It's best not to denormalize such data into another table. If you need to query it, you can just do it on the fly from the Purchase table. Use a view if you want, and even index it.

CREATE VIEW dbo.vTotalStock
WITH SCHEMABINDING   -- an indexed view must be schema-bound
AS
SELECT
  p.ItemId,
  qty = SUM(p.qty),
  count = COUNT_BIG(*)  -- must always include COUNT_BIG if aggregating and indexing
FROM dbo.Purchases p
GROUP BY
  p.ItemId;

CREATE UNIQUE CLUSTERED INDEX UX ON vTotalStock (ItemId)

db<>fiddle