1

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).

  • Can you explain what the trigger is doing that it has to reference the table by name? – Aaron Bertrand Apr 23 '13 at 21:21
  • Also, is there any way the trigger functionality can be implemented without triggers, or that you could automate the dropping/re-creating? It seems like both rename and ALTER SCHEMA TRANSFER produce this identity crisis. – Aaron Bertrand Apr 23 '13 at 21:53
  • I think I just figured it out. Do you see any reason not to have both triggers point to the prod table? There is no reason for it to point to the stage table since no updates are being done on that table, do you? – Kenneth Fisher Apr 23 '13 at 21:58
  • 1
    Do you feel that ALTER SCHEMA TRANSFER would be a better/faster method than sp_rename? – Kenneth Fisher Apr 23 '13 at 22:11
  • 1
    Well I like it better for a few reasons (I blogged about it here, but didn't handle triggers), but speed isn't one of them. And my idea would work with either method I think. – Aaron Bertrand Apr 23 '13 at 22:14
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.