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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Triggers to Track Database Action History
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 09/18/2001 :  21:50:54  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 Posts

Posted - 09/26/2001 :  10:27:10  Show Profile  Reply with Quote
This is an excellent article!! In fact, I just implemented this into my own work. I like the fact it keeps the whole record tracked so I can make easy reports showing changes as whole records. Thanks!!

Go to Top of Page

Starting Member

1 Posts

Posted - 08/15/2002 :  04:12:48  Show Profile  Reply with Quote
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 - 08/15/2002 :  08:36:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 Posts

Posted - 04/20/2005 :  05:31:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000