How to delete on cascade a self reference relationship?

Question

I have a table that has a autoreference relationship, like that:

TableA(IDTableA, IDTableARoot,...)

and I have this instead of delete trigger to delete:

Delete from TableA where IDTableARoot IN(select deleted.IDTableA from deleted)
    Delete from TableA where IDTableA IN(select deleted.IDTableA from deleted)

I guess that I have this two rows:

Row 1: IDTableA: 1 IDTableARoot: 1

Row 2: IDTableA: 2 IDTableARoot: 1

When I try to delete the row 1:

delete from TableA where IDTableA = 1;

Only it is deleted the row with ID = 1, but not the row with ID = 2, but it shuld be deleted bacuse in the first statement of the trigger I say that I it has to be deleted the row with IDTableARoot = IDTableA where IDTableA is the ID of the row that I am deleting.

So, how could I delete in cascade rows that belong to a self relationship?

Thank you so much.

Answer 1

You are trying to do another DELETE in a DELETE trigger which is disallowed as this can go into an infinite loop.

You might want to change your use a INSTEAD OF DELETE trigger (see this link) and change your trigger body code to something like below

UPDATED: To address the error that @TT pointed out.

CREATE TABLE #CascadeDeleteRows (IDTableA int, IDTableARoot int)

INSERT 
  INTO #CascadeDeleteRows 
SELECT b.IDTable
     , b.IDTableARoot 
  FROM TableA
 WHERE b.IDTableA IN (SELECT deleted.IDTableARoot from deleted)

DELETE 
  FROM TableA 
 WHERE IDTableA IN (SELECT #CascadeDeleteRows.IDTableA FROM #CascadeDeleteRows)

DROP TABLE #CascadeDeleteRows

Hope this helps