It was that long list of table joins without a line break that spooked me!

Here are the changes I made:
(1) COALESCE instead of CASE makes the code...

Please post your code your code (and for the sake of your colleagues and future self, lay it out) in a format that's easy to read.  It's difficult for others...

My suggestion is not to call jobs from triggers.  What happens if a job is already running when the trigger that calls it fires?  What if SQL Server Agent is...

You've posted in the SQL Server 2008 forum.  Is that the actual version you're using?  If so, I don't think there's anything you can do, short of capturing all plans...

The creation_time column - is that what you're looking for?

John

You've posted in the SQL Server 2008 forum - is that the actual version you're using?  If you're on SQL Server 2012 or above, you can use TRY_CONVERT.  If you...

Understood, but if it were me I'd rather prevent any anomalies than try to capture and correct them after the fact.  In the worst case, there could be hundreds or...

If you're deleting 20 million rows one at a time, of course it's going to be slow.  Do it in one go - that's what RDMSs are designed for.  If...

Your programmers ought to be in the habit of adding comments to an object definition every time they create or alter it.  You can quality check this yourself, since you...

Save it as a .sqlplan file and attach it to your post.

John

I don't know whether automatic failback is even a thing.  But even if it were, how would you want it to work - for failback to occur immediately the primary...

Why are you doing this row by row instead of as a set.

When you insert a NULL value, it doesn't have inverted commas round it in the INSERT...

Yes, or create the temp table before you start, instead of doing a SELECT INTO.  I always think that's safer.

John

Do you have an actual execution plan from a long-running delete that you could post here, please?

John