Delete trigger on multiple rows

Question

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?

Answer 1

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)

Answer 2

You can delete multiple rows. But you have to put multiple id. For example:

DELETE FROM  VisitorMaster WHERE Id=85 or Id=86