sql - AFTER DELETE trigger does not always fire -

i have tables a, b, c. has nullable fk b called bid , b has fk c cascade delete.

when c deleted, need bid on set null. when delete on c cascades b, expect following trigger execute:

create trigger after_delete_b on b delete  begin     declare @bid int     select @bid = id deleted     update     set bid = null     bid = @bid  end

however seems execute , not others. cannot figure out why.

your trigger not handling multiple row deletes, captures 1 id deleted rows , update related value in table a, since there 1 variable,

you need use set based approach handle multiple deletes.

for need modify trigger definition this.....

create trigger after_delete_b on b delete  begin  set nocount on;     update     set a.bid = null        inner join  deleted d on a.bid = d.id  end

Ruby Co