You should seriously consider specifying "ON DELETE SET NULL" in the FK relationship and let SQL do this for you rather than writing it yourself.
Curiosity question... would using INSTEAD OF as opposed to FOR, and then ensuring no recursion, and then doing the delete yourself be a viable alternative when combined with your recommendation? Haven't had opportunity to work with FK delete scenarios as yet and am curious what experience you may have with that...
Yes, in theory you can do something like that. But then next week you add another table in the chain and you have to change the order of UPDATEs in your code again. I use context info settings to avoid trigger recursion conflicts when I need to, but I still prefer to let SQL do this automatically when it's applicable, i.e., when I want a key set to NULL rather than to prevent the parent DELETE from occurring (the default setting).
Good reason! Might limit the applicability to very stable systems where change is concerned, but at least I now know a better alternative. Thank you, kind sir!