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!