Delete触发器with Update Subquery语句with Multirow

Question

此触发器适用于单行delete，但适用于多行

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

Answer 1

Deleted伪表中可以有0-N行，您需要对其进行处理。像所有的T-SQL一样，您希望尽可能使用完全基于集合的方法，因为这是SQL Server优化的目标。

我相信下面这些应该可以实现你想要的效果。

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;