>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,
>TIA,
>Viv

Viv,

I know it's an old thread, but may be this advice from Lous Davidson can be relevant to your situation (even though I suspect you solved it already) http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/81d592f1-f380-4e4e-a91f-2dde32bb4a62