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.
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!! :)
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?
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.