I want to make sure people realise that enabling and disabling a trigger (usually as part of an archiving procedure) is completely normal and safe.

There is alot of disinformation about...

What utter nonsense

altering a table within a transaction requires an exclusive lock on the table

so nobody can change the table in any way once the trigger is disabled (DDL or...

Please see <<< questions below ...

SET XACT_ABORT ON <<< will this affect try catch whether ON or OFF?

GO

BEGIN TRY

BEGIN TRAN

...

Thanks for the detailed description of those gotchas

Did you have any thoughts on creating a transactional script with multiple CREATE/ALTER VIEW/PROC/TRIGGER/FUNCTION statements?

Is the exec('alter view') approach a good one /...

I *think* the problem stems from the fact the statement MAY return more than one record

rewrite it so it can only EVER return one record and it might be fine

select...