CODEWITHSUNDEEP
sqlsql-servertriggerscascading-deletes
user1569339
user1569339

Reputation: 683

AFTER DELETE trigger does not always fire

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.

Upvotes: 0

Views: 169

Answers (1)

M.Ali
M.Ali

Reputation: 69514

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

Upvotes: 2

Related Questions