Using Triggers on a table renamed with sp_Rename

[LucieLastic]

hi

I have a main table and a 'copy' table. The main table has a trigger on it. Each day the copy table is loaded with the new data and then it is renamed (using sp_rename) to be the main table and the main table is renamed to the copy table.

I drop all the indexes and primary keys on the tables before I call sp_Rename and then put all the indexes back on the newly renamed table.

Do I need to drop the trigger too, and then recreate that after the sp_Rename?

I tried to put it in the stored procedure which does all the dropping and renaming etc of the tables but it needed to be at the beginning of the batch and I want it at the end.

The trigger is on the UPDATE and writes changes to a log table. I do a huge update statement in the middle of the stored procedure (500000+ rows) before doing the sp_rename so it needs to be after this.

So, do I need to drop it and recreate it when using sp_rename?
And can I put it in a second stored proc and call that at the end after calling all my renaming processes/SPs?

Hope that makes sense. Many thanks for help,
lou

 

[SQLSister]

Just why do you have a trigger on a table that you are populating by recreating the table anyway?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[LucieLastic]

I'll have a go at explaining what we're doing:

This table contains settings information for particular items but it can not automatically pick up any new items from another system which may have been added. Also, these settings change throughout the day. Also, some settings become obsolete so the easiest method is to rebuild the table with the Items from the other system. The other system has a master list of Items which can change extensively throughout a day.

Each morning
The Copy table is emptied and all the Items including new ones, are extracted from the other system and added to the Copy table. New Items are given default settings.

An Update is performed between the Main table and the Copy table to align all the settings for the Items in the Copy table.

The two tables are swapped over (with sp_Rename) so the newly built Copy becomes the live main table.

I want the trigger to log all changes to the live table.

We decided on this method of updating the table as it seemed the fastest and most efficient way to have the correct list of active Items. Building the Copy table takes a few minutes.

 

[SQLSister]

OK. Being aware that nothing you bring in during the main recreate table process will kick off the trigger.

Personally waht I would do is put this in a DTS package.

Script out everything that is on the main table. Make sure as part of scripting that you generate a drop object for each thing.

in your DTS package, create the work table and populate it. Then run an execute SQL task wwhihc has all the drop objects statements (except drop table of course) in it and then has the rename table SQl in it.

Next add another execute SQl task to recreate the trigger and then the other objects which need to be recreated (indexes, foreign keys, constraints)

then once the DTS packe is done and tested on a development server, schedule it to run on production at night. If you use SQl mail, have it mail you the results of the package, so you will now if it ever fails.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief