Hi,

I am thoroughly confused about the relationships between foreign key constraints (with cascading deletes) and the 'INSTEAD OF DELETE' and 'AFTER DELETE' triggers.

The mess I'm facing is far more elaborate but this is a simpler example of my problem:

Three tables e.g. TableA, TableB, TableC

TableA causes a cascading delete to TableB ; TableB a cascading delete to TableC. But this is complicated by the fact that TableB has a 'self-referencing' relationship on its primary key (information used to construct a TreeView). To deal with a delete in TableB I have to do a recursive check for all child nodes and delete those as well. In isolation I can dealt with this using an 'INSTEAD OF DELETE' trigger such as:

CREATE TABLE #Table (Id  UNIQUEIDENTIFIER )
    INSERT INTO #Table (Id) Select Id FROM deleted

    DECLARE @c INT
    SET @c=0

    WHILE @c <> (SELECT COUNT(Id) FROM #Table) BEGIN
     SELECT @c = COUNT(Id) FROM #Table

       INSERT INTO #Table (Id)
       SELECT Sheets.Id FROM Sheets
       LEFT OUTER JOIN #Table ON Sheets.Id = #Table.Id
       WHERE Sheets.ParentId IN (SELECT Id FROM #Table)
       AND #Table.Id IS NULL
    END
    DELETE Sheets FROM Sheets INNER JOIN #Table ON Sheets.Id = #Table.Id

But I read that the 'INSTEAD OF DELETE' is ignored if the delete is triggered by a FK constraint. If that's true then I can't just set a cascading delete on TableA-TableB either. Also, even if that worked, I don't know if I can rely on just using a cascading delete from TableB - TableC.

And I've *no* idea whether 'AFTER DELETE' would be a better (or even usable) option?

Two other factors:
In my real world example it's possible that TableC will also be self-referencing and need to cascade deletes.
I want to use standard cascading deletes whereever possible so that the behaviour is automatically picked up by EF

I'm probaby going to build a simplified DB model and try things out but any suggestions and/or light-shedding much appreciated,
Viv