sql-server-2008 - 删除多行触发器

以下是我的触发器：

ALTER TRIGGER [dbo].[trgPaxeDeleted]
   ON  [dbo].[paxe]
   AFTER DELETE
AS
declare
@HFflightID int,
@RFflightID int

BEGIN
        Select @HFflightID = hfFlightID, @RFflightID = rfFlightID from deleted

        -- Hinflug: flugKontingent hochsetzen --
        UPDATE    flightdata
        SET       flightQuota = flightQuota + 1
        WHERE     (flightID = @HFflightID)

        -- Rückflug: flugKontingent hochsetzen --
        UPDATE    flightdata
        SET       flightQuota = flightQuota + 1
        WHERE     (flightID = @RFflightID)

END

这在删除单行时效果很好。但是，当删除多行时，仅完成一次触发操作。

我该如何改变？

最佳答案

SQL Server触发器为所有行触发触发器的一个实例。 deleted表将包含所有要更新的行，因此您不能在变量中存储单个实例。

摆脱下面的代码行：

Select @HFflightID = hfFlightID, @RFflightID = rfFlightID from deleted

您的更新声明应如下所示：

UPDATE    flightdata
SET       flightQuota = flightQuota + 1
WHERE     flightID IN (SELECT hfFlightID FROM deleted)

UPDATE    flightdata
SET       flightQuota = flightQuota + 1
WHERE     flightID IN (SELECT rfFlightID FROM deleted)