sql server - How to delete on cascade a self reference relationship? -

i have table has autoreference relationship, that:

tablea(idtablea, idtablearoot,...)

and have instead of delete trigger delete:

delete tablea idtablearoot in(select deleted.idtablea deleted)     delete tablea idtablea in(select deleted.idtablea deleted) 

i guess have 2 rows:

row 1: idtablea: 1 idtablearoot: 1

row 2: idtablea: 2 idtablearoot: 1

when try delete row 1:

delete tablea idtablea = 1; 

only deleted row id = 1, not row id = 2, shuld deleted bacuse in first statement of trigger has deleted row idtablearoot = idtablea idtablea id of row deleting.

so, how delete in cascade rows belong self relationship?

thank much.

you trying delete in delete trigger disallowed can go infinite loop.

you might want change use instead of delete trigger (see link) , change trigger body code below

updated: address error @tt pointed out.

create table #cascadedeleterows (idtablea int, idtablearoot int)  insert    #cascadedeleterows  select b.idtable      , b.idtablearoot    tablea  b.idtablea in (select deleted.idtablearoot deleted)  delete    tablea   idtablea in (select #cascadedeleterows.idtablea #cascadedeleterows)  drop table #cascadedeleterows 

hope helps