AFTER DELETE trigger does not always fire

Question

I have tables A, B, C. A has a nullable FK to B called BId and B has a FK to C with cascade delete.

When C is deleted, I need BId on A to be set to NULL. So when DELETE on C cascades to the B, I expect the following trigger to execute:

CREATE TRIGGER AFTER_DELETE_B
ON B
FOR DELETE
AS

BEGIN

   DECLARE @bId INT

   SELECT @bId = Id FROM DELETED

   UPDATE A 
   SET BId = NULL 
   WHERE BId = @bId

END

However this seems to execute sometimes and not others. Cannot figure out why.

Answer 1

Your Trigger is not handling multiple row deletes, it only captures one ID from the deleted rows and update the related value in table A, since there is only one variable,

You need to use a set based approach to handle multiple deletes.

for that you will need to modify you trigger definition something like this.....

CREATE TRIGGER AFTER_DELETE_B
ON B
FOR DELETE
AS

BEGIN
 SET NOCOUNT ON;

   UPDATE A 
   SET A.BId = NULL
   FROM A 
   INNER JOIN  DELETED D ON A.BId = D.Id

END