I rather dislike SQL Server's lumping together of multiple cascade paths and cascade cycles; it shouldn't be forcing us to use triggers in all cases where there can be multiple cascade paths. I'm perfectly happy for it to refuse to handle cascade cycles because I think a that it's potentially possible to obtain different results according to what order updates and/or deletions take place, but I would prefer to see it hadling multiple cycle-free cascade in cases where it's easy to detect (from the metadata) that different resuts can't be produced by different operation orders (as in the example in this case).
Agreed. I dislike cascading to begin with, but won't support multiple paths. I especially dislike cascade delete.