SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

thebillysmith
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

fergusn
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

robvolk
Most Valuable Yak

USA
15678 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

Fayyaz
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 twice..so it adds 2 before updates and 2 after updates...????
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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