November 18, 2004 at 6:38 am
I am getting ready to build a large SQL Server based application. The users are going to want records archived as they update/insert records into the application.
My first (and easiest) solution is to set up every table with an associated archive table. The archive table would have its own unique, autoincrementing integer key. Then put a trigger on the original table to say "Whenever there is an insert, update, delete) copy the previous record into the archive table". This will involve putting triggers on just about every table I create.
Does anybody have any other ideas on how to do this? Just looking for other suggestions as to how to solve this.
November 18, 2004 at 7:28 am
So the archive always contains a log of the changes that have been made to a record?
The trigger method does mean that the archiving is done at the database level so even someone messing around in EM or any other app is going to fire the triggers.
One alternative is to encode your archiving routines within stored procedures. This means that the archiving will only happen in response to explicit calls to those stored procedures.
An alternative approach would be to have an updated/created date on your records and have a batch job that runs every night to copy old versions of records into your archive tables. If your app is large then this approach may give you better overall performance in the long run.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply