Home Forums SQL Server 2014 Development - SQL Server 2014 running trigger before deletion RE: running trigger before deletion

  • March 13, 2018 at 12:40 pm

    #1983041

    sgmunson - Tuesday, March 13, 2018 12:17 PM

    ScottPletcher - Tuesday, March 13, 2018 12:00 PM

    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).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.