Updating Table in Delete Trigger for Multiple Rows Deleted

Question

I started with the following trigger in SqlServer 2000

 ALTER TRIGGER DeleteTrigger
 ON [dbo].[WarrantyClaimsLineItems]
 FOR DELETE
 AS 
 begin

    declare @wa int
    SET @wa = (SELECT warrantyAuthNo from DELETED)
    update dbo.warrantyclaimsauth
    set isused = 0
    WHERE warrantyClaimsAuth.warrantyauthno = @wa


end

Basically, whenever a line item is deleted from the table I need to update the warrantyauth.isUsed that was assigned to that line item back to false.

it works when deleting one line item but when multiple line items are deleted I get the following error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I understand why, the trigger is expecting only one record to be deleted. so I need to modify it to handle multiple row deletes.

I did some research and modified my trigger to be this

....
 AFTER DELETE
 AS 
 begin

    update dbo.warrantyclaimsauth set isUsed = 0
    from dbo.warrantyclaimsauth a
    inner join deleted d on a.warrantyAuthNo = a.warrantyAuthNo


 end

this however is updating every row in the warrantyclaimsauth with 0 in the isUsed field. Can anyone help me with what I am doing wrong? Thank you!

Answer 1

Your join condition is wrong in the second one. Try this:

 AFTER DELETE
 AS 
 begin

    update a set isUsed = 0
    from dbo.warrantyclaimsauth a
    inner join deleted d on a.warrantyAuthNo = d.warrantyAuthNo


 end

You were joining deleted where the warrantyauthno equalled itself, rather than the value from the deleted table. You were essentially just doing a cross join of deleted and not filtering any rows what so ever.

Answer 2

inner join deleted d on a.warrantyAuthNo = a.warrantyAuthNo

should be

inner join deleted d on a.warrantyAuthNo = d.warrantyAuthNo

You currently have a trivial join condition giving a cross join which doesn't filter to deleted