I've just discovered that instead-of-delete triggers aren't recursive. That is, if you have an instead-of-delete trigger on a hierarchical table and you try to delete a record that has more than 1 level of children, it breaks.

Example: I have this structure:

A
 - B
   - D
   - E
 - C

When I delete A, an instead-of-delete trigger deletes all the rows whose idParent is A (B and C). I expected this to cascade on down and delete rows D and E also, like a cascading delete on a foreign key would. It doesn't. It just throws a foreign key violation. I grubbed in the SQL Server documentation and found that that is an acknowledged limitation.

My question is, how have you gotten around this ridiculous limitation? It makes instead-of-delete triggers useless. I can't use a cascading foreign key because of SQL Server's similar ridiculous inability to handle multiple and circular cascade paths.

Thanks,
K