How to add a trigger to copy table to archive table

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » How to add a trigger to copy table to archive...

How to add a trigger to copy table to archive table

Rate Topic

Display Mode

Topic Options

Author

Message

halv235

Posted Tuesday, November 03, 2009 2:45 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 30, 2009 9:17 AM
Points: 12, Visits: 19

I have a database that I want to start archiving. It contains 2 tables that I am going to archive to a new archive_database. I want to add a trigger to the current tables to automatically upadate the archive table for updates and inserts. I will then purge off data that is over n days on the current database and the archive_database will always show complete history.

I don't have any experience with triggers and am looking for suggestions.

thanks.

Post #813253

GSquared

Posted Tuesday, November 03, 2009 2:48 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,681, Visits: 4,953

Don't do it in a trigger. Set up a job that archives data on a scheduled basis. That almost always works better.

- GSquared

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #813257

Elliott W

Posted Tuesday, November 03, 2009 3:16 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 2,406, Visits: 1,869

I'm with GSquared on this, doing it in a trigger is likely to cause unforseen issues.

You are aware that triggers are synchronous, meaning that they have to complete before whatever caused them to run gets control back, in other words the update statement that fired the trigger will be sitting there waiting until the trigger gets all the way done with whatever it is doing.

Because you can't know how much will be archived in any single execution this creates a case where performance will be indeterminable and sometimes will be ok and others less so but you can't predict it ahead of time. From a users perspective this is not good.

Best answer, create a stored procedure that performs the archival and using SQL Agent schedule it at least once a day.

CEWII

--------------------------------
Having trouble figuring out what jobs are running in SQL Server at the same time.
Try Sql Job History Visualization
It lets you view your SQL Job history on an Outlook style calendar..

Post #813285

DiverKas

Posted Thursday, November 19, 2009 9:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 9:13 AM
Points: 139, Visits: 140

G-Squared is right. I would NEVER recommend a trigger in this situation for a more insidious reason than mentioned above. Since triggers can be fired simultaneously from multiple SQL statements, you could, depending on the level of transaction handling you choose, either A) create masses of duplicate archive records for each trigger firing, B) deadlock the processes because each is trying to transact the archival of the records.

This is definitely a Job that should be scheduled off hours.

Post #821710

saddamkhan74

Posted Friday, November 27, 2009 12:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 27, 2009 5:27 AM
Points: 2, Visits: 2

The easiest thing to be done is to back up the database on daily basis so whenever anything wrong happens you would still have a backup from the day before.

If the database is not that big you can do 2 backups (automated) a day.
magic jack
buy magic jack
magic jack review

Post #825469

« Prev Topic | Next Topic »

Permissions