Sql-server – Transaction control to change triggers

sql server

I need to create a trigger in a specific table that already has another trigger.
What I want to do is:

  • Disable the old trigger
  • Enable the new trigger (In a new history table)
  • Populate the values from the previous history table to the new one.

How should I control the transaction in this case to make sure I will not mess up the data with both tables?

I was considering using TABLOCK hint, so it would be something like this:

BEGIN TRAN

    SELECT 1
    FROM  table
       WITH (TABLOCK, HOLDLOCK)

--Disable the old trigger
--Enagle the new trigger
--Populate the data to the new table    
COMMIT

Does it make sense?
Another question: Is there a way to automaticly add a column in the history table (trigger table) in case we add a new column in the main table?

Thank you in advance!

Best Answer

In my testing, this DID lock the table after I executed the disable for the old trigger up until I committed my transaction. I had to put the GO statements in before disable/enable to keep SSMS from fussing.

begin transaction 
go
--Disable the old trigger
go
--Enable the new trigger
go
--Populate the data to the new table    
commit

As far as automatically adding a new column to the history table upon adding a column to the main table, see Aaron Bertrand's post on how this 'might' be achieved (and caveats) using DDL triggers.

Related Question