CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
hsn
hsn

Reputation: 31

Delete Trigger with Update Subquery Statement with Multirow Delete

This trigger is working fine with single row delete, but on multiple row

CREATE TRIGGER [dbo].[AfterDeleteStockUpdate] 
ON [dbo].[ItemLedger]
AFTER DELETE
AS 
    DECLARE @productId uniqueidentifier

    SELECT @productId = del.Product 
    FROM deleted del;

    UPDATE products 
    SET inStock = (SELECT SUM(ISNULL(inQty, 0)) - SUM(ISNULL(outQty, 0)) 
                   FROM itemledger 
                   WHERE product = @productId), 
        PurchasedValue = (SELECT SUM(ISNULL(PurchaseAmount, 0)) - SUM(ISNULL(Amount, 0)) 
                          FROM itemledger 
                          WHERE product = @productId) 
    WHERE id = @productId

Upvotes: 0

Views: 163

Answers (1)

Dale K
Dale K

Reputation: 27198

The Deleted pseudo-table can have 0-N rows in it, which you need to handle. And like all T-SQL you want to be using a fully set-based approach wherever possible anyway as thats what SQL Server is optimised for.

I believe the following should accomplish what you are wanting.

CREATE TRIGGER [dbo].[AfterDeleteStockUpdate] 
ON [dbo].[ItemLedger]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE P SET
      inStock = L.inStock
      , PurchasedValue = L.PurchasedValue
    FROM Products P
    INNER JOIN (
        SELECT SUM(ISNULL(L.inQty, 0)) - SUM(ISNULL(L.outQty, 0)) inStock
            , SUM(ISNULL(L.PurchaseAmount, 0)) - SUM(ISNULL(L.Amount, 0)) PruchasedValue
        FROM itemledger L
        GROUP BY L.product
    ) L ON L.product = P.id
    WHERE P.id IN (SELECT Product from Deleted);

END;

Upvotes: 2

Related Questions