Delete trigger on multiple rows

sql-server-2008, t-sql

Solution

SQL Server triggers fire one instance of the trigger for all rows; the `deleted` table will contain all rows being updated, so you can't store a single instance in a variable.

Get rid of the following line of code:

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

Your update statment should look like this:

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)

Problem

Below is my trigger: ``` 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 ``` This works fine on deleting a single row. But when deleting multiple rows only one trigger operation is done. How do I change this?

Original source