update trigger - update stock level based on count of rows changed-CodePudding

I want to keep track of stock levels using AFTER UPDATE triggers in SQL Server 2019.

I have this table Units (unique items with serial numbers), table simplified for this example:

UnitID	ProductID	SalesOrderDetailID	SerialNumber
1	1	Null	12
2	1	Null	34
3	1	Null	56
4	2	Null	78
5	2	Null	90
6	2	Null	99

And this table for Stock:

ProductID	StockSell	Updated
1	10	1.1.2022 10:10:00
2	45	3.10.2022 22:48:05

I want to use an AFTER UPDATE trigger to update the Stock table when I assign a SalesOrderDetailID to a Unit. I managed to write the trigger for a single row update using parameters, but that's not good.

I have this at the moment:

CREATE TRIGGER tr_UnitUpdated
ON tbl1Units
AFTER UPDATE
AS
BEGIN
    UPDATE tbl1Stock
    SET StockSell = StockSell - 1
    FROM tbl1Stock S    
    JOIN INSERTED I ON S.ProductID = I.ProductID
    JOIN DELETED D ON I.UnitID = D.UnitID
    WHERE I.SalesOrderDetailID IS NOT NULL 
      AND D.SalesOrderDetailID IS NULL
END

The problem is that when I assign SalesOrderDetailID to more Units in a single operation, the Stock level only reduces by 1, which after a short investigation is quite obvious because of the SET StockSell = StockSell - 1 statement.

How should I edit this statement for it to deal with bulk updates correctly?

Using the example above, if I scan units 4, 5 and 6 into a particular sales order and edit the Units table in bulk, I want the Stock Level for Product = 2 to change from 45 to 42.

Thanks in advance.

CodePudding user response：

As the updates may affect more than one ProductID, you need to GROUP BY ProductID before joining to tbl1Stock.

UPDATE S
SET    StockSell = S.StockSell - I.Cnt
FROM   tbl1Stock S    
       INNER JOIN
       (
           SELECT I.ProductID, Cnt = count(*)
           FROM   INSERTED I 
           WHERE  I.SalesOrderDetailID IS NOT NULL
           GROUP BY I.ProductID
       ) I ON S.ProductID = I.ProductID

Note : I didn't include the DELETED as I don't quite understand your logic on DELETED.SalesOrderDetailID IS NULL