Using a table swap for a data load but I have triggers

Question

I'm working on re-writing a table load. The current method is a daily job that deletes from the table, then re-loads it. The complaint is that if the load fails the users have no data. The users have said they are ok if the load fails and they have to use yesterdays data.

My solution is to do a table swap.

1. Create a staging table that has exactly the same structure, indexes etc
2. Then the daily job does the following
   1. Delete from the staging table
   2. Load the staging table
   3. Begin a transaction to keep everyone out of the tables until I'm done
   4. Rename Live to Backup
   5. Rename Staging to Live
   6. Rename Backup to Staging
   7. Commit the transaction

This works fine on 3 of the 5 tables I'm working with. The other 2 have triggers. Because the trigger has code that references the table name (of course) it can't handle the rename of the object it is attached to.

I start with this:

Table1_Live
  |
  -- Trigger for Table1_Live

Table1_Stage
  |
  -- Trigger for Table1_Stage

After I do my renames I get the following

Table1_Stage
  |
  -- Trigger for Table1_Live

Table1_Live
  |
  -- Trigger for Table1_Stage

The benefits to the method are that the down time for the users is minimal. I have yesterdays data. And if anything goes wrong I roll back to yesterdays data.

My question is does anyone know a better method without the trigger problem, or a way that I can swap my triggers between the two tables so that the code remains correct after the rename?

Oh, and I'm somewhat embarrassed to admit but even though the database is 2005 (we are upgrading soon) the requirement is that the load be done using a DTS package. My plan is for the rest of the process to be done in a stored procedure that can be called from the DTS package (or SSIS if I can ever talk them into it).

Answer 1

This is a pretty crappy workaround, since there really should be an easy way to rename a table or transfer it to a different schema and keep its triggers (I've put in a bat-signal to an MS contact to see about this). But I think it's better than carrying around triggers on both tables that have the live name hard-coded.

Drop the trigger on the stage version of the table. This isn't the one people are touching. Then alter your process slightly:

• Step 3.5:

  DECLARE @sql NVARCHAR(MAX);
  SELECT @sql = OBJECT_DEFINITION(OBJECT_ID('dbo.table1_trigger_name'));
  DROP TRIGGER dbo.table1_trigger_name;
  

• Step 6.5:

  EXEC sp_executesql @sql;
  

This will re-create the trigger on the right version of the table. And actually, contrary to my comment, does not require the trigger code to be stable over time, since every time you run it, it will grab the current definition.

Now, you will not be able to have any GO / batch separation between those steps, and you should regularly keep a copy of the trigger in source control just in case anything every goes wrong.