Thoughts on User Transaction logging.

[StormbringerX]

Hi all,

SQL200, VB6, Win2000.

The application I'm currently working on requires (government specs) that I create a log for every table modification in the database. Specifically: UserName, MachineName, DateTime, Table modified, Type of modification (Insert, Delete, Update) and what was changed. In addition, a specifically appointed user would have to be able to access and print the information. (The location of the software is off-site, so at least one person will need to be trained to perform these duties)

I have looked at the Transaction Log (dbcc log) and this seems to be much, much more than I need (never mind the headache of figuring out what's what). I've also looked at 3rd party software, but again, it seems to be much more information than is needed.

My thoughts are a trigger on each Table that would simply write the information to a Log Table defined in the database. However, I don't know if this is the best way. Keep in mind that this information will not be used for restores or anything of that nature. It's only purpose is to show when something was changed.

I'm curious as to how others have handled a similar situation and would like to hear about it.

THanks, Dave

 

[JayKusch]

As a first thought ... That table could get VERY large if your DB is used to any great extent.

Thanks

J. Kusch

 

[StormbringerX]

Thats very true Jay. The initial plan is that once a month (that may change, but it's a starting point) the log table would be printed then purged.