Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Article on Archiving and Auditing?

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2002-04-30 : 09:22:09
Jason writes "Hi Sql Team, thanx for your invaluable resource.

Could you please write an article on archiving and auditing and how it might impact the design process. Here is a scenario for you:

A large organisation has a database for employee details. It requires that any change to an employee record should still allow an old copy to be kept as well as the new (ie 2 records).
All information must be kept for x amount of years as per legal requirements. Also date/time and whoever made the change should be recorded as well.


Auditing - should every table in the db have a date/time modified and "who by" field tacked on the end, or should a single audit table be used to keep a record of all changes?

Archiving - when a change is made to an employee record, should it in fact create a new record (insert not update) and perhaps set a "status" field to "0" on the old record so that an automated process could later extract it to an archive db/table?

Some organisations think a weekly backup is sufficient for their archiving needs. This can be a problem if a record is modified on Monday, then modified again on Thursday, and the backup takes place on Friday - the value of the record between Monday and Thurs wont appear in the backup.

Well, I hope all that makes sense!
I hope you guys can shed some light on these issues.


Melbourne Australia"

SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-30 : 13:29:53
I agree this would probably be a great topic for an article, but I'm not prepared to write it. So, in the meantime, here are a couple of thoughts...

1) I would think a separate audit table (whether one for all changes, or one per main table is debatable, and probably depends on the activity level) would be needed so you could keep the complete history of changes and archive old history as desired. You still might want the date/time modified and by whom fields on the main table if you need to query this info frequently, otherwise it can be extracted from the audit table.

2) Using a separate audit table and tracking all changes solves the "weekly backup" issue because both changes are stored in the history.

3) One potential challenge with INSERT instead of UPDATE is primary keys. If the primary key won't change, then it's really an UPDATE anyway. If the Primary Key WILL change, then you have the added headache of modifying all dependent objects. SQL 2000 has Cascading Updates as an option and there's an article here on how to do the like in SQL 7, but it can still all be a headache.

Edited by - AjarnMark on 04/30/2002 13:30:27
Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 14:17:43
This just in:

Go to Top of Page

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-05-07 : 15:54:17
Here's a pretty in-depth article describing how to use triggers to create valid time audit tables :


Dave Kawliche
Go to Top of Page

- Advertisement -