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
 Site Related Forums
 Article Discussion
 Article: Using Triggers to Track Database Action History

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2001-09-18 : 21:50:54
Several State and Federal contracts that I have worked on in the past have wanted to keep track of all changes to a particular record, such as who modified the record, what kind of modification took place, and when the record modification occurred. I decided to solve this problem with triggers because I wanted to capture every action that occurred on a particular table.

Article Link.

Starting Member

1 Post

Posted - 2002-08-15 : 04:12:48
Would it not be nice to have some functionality where we have the ability to read the transactions log? I have seen these trigger update logs and they work but can cause problems when archiving data or performance problems when doing big deletes, updates and inserts. There is also data duplication as this information is already stored in the transactions log. Transactional replication has a built in log reader all that is needed is to convert this into a function and then have the ability to link it to a row in the database. Hello Microsoft what do you think?

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 08:36:49
No, that is not a viable solution. The transaction log is not meant to archive data, and it needs to function regardless of whether replication is used or not. It provides a means to ensure transactional integrity. If you perform a log backup and truncate the log, your "archive" would be lost, replication or no replication. You'd have to put the audited data into another table anyway, and trying to reconstruct that from the log records is far more difficult than simply using the trigger method discussed in the article.

Go to Top of Page

Starting Member

1 Post

Posted - 2005-04-20 : 05:31:12
I have used the trigger code that was posted in that article...but it adds everything it adds 2 before updates and 2 after updates...????
Go to Top of Page

- Advertisement -