November 3, 2009 at 2:45 pm
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.
November 3, 2009 at 2:48 pm
Don't do it in a trigger. Set up a job that archives data on a scheduled basis. That almost always works better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 3:16 pm
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
November 19, 2009 at 9:11 am
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.
November 27, 2009 at 12:20 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply