>Hi,
>
>I have a table with a 'self-reference' relationship (Foreign Key: ParentId, Primary Key:Id)
>
>I know that I cannot set cascading deletes on this because of potential recursion problems.
>
>I tried a DELETE trigger instead which works IF the relationship is removed. But I need to keep the relationship in place for use with the EntityFramework. With the relationship in place the trigger fails because it is removing items in the wrong order (ie deleting an item before deleting the children)
>
>So, instead, I tried using an INSTEAD OF DELETE trigger which deletes the children before the parent and this works OK.
>
>BUT I need to define a cascading delete relationship between this table and another and I can't do that when the INSTEAD OF DELETE trigger is in place.
>
>I'm stuck! Any suggestions,

I found something that works:
I changed the delete action between the tables to 'Set Null' and added a AFTER DELETE trigger to the PK table to delete all rows in the FK table where the key value was null.
Feels horribly clunky. I hate it. But it works. Anything better?

I also discovered that the INSTEAD OF DELETE trigger is not called recursively but found this solution:
http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/
Again not nice but it works. Again - anything better?
Pity there's no BEFORE DELETE trigger......
TIA,
Viv