Best way to track changes to a record?

[gaffonso]

I'm designing a database in which I track changes made to each record. I've currently got a scheme whereby each table has a "LastModifedID" and a "LastModifiedDateTime" column. I intend to populate these with insert and update triggers.

Is there a better way to do this? I know that SQL Server has a a logging feature, is the log data available via queries? Do the logs track login name as well as modification time? Are the logs record specific (seem unlikely but I figured I'd ask)?

Pointers to the appropriate references would be great.

Thanks!

- Gary

 

[tlbroadbent]

SQL Server logs are not audit trail logs. SQL Server uses the transaction log of each database to recover transactions or to rollback if a transaction is cancelled. Generally when a backup occurs the transaction log is truncated so it typically contains only most recent transactions.

Although, you can read logs using the undocumented extended stored procedure, xp_readerrorlog, I've not found these logs very useful as audit trails. There are a few third party log reader tools available.

At this point, MS hasn't seen fit to provide audit trail capability with SQL Server. Usually developers design their own audit trail process. Or they purchase a third party audit solution. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.