November 18, 2002 at 8:35 am
Is it possible to keep track of changes made in columns. The information I want to store in a loggfile is date, time, user, old value, new value...
Should I use triggers or is there another way to get the job done?
November 18, 2002 at 8:46 am
Triggers are one option. Lumient log explorer is another.
With triggers you are best to log to another table.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 18, 2002 at 11:00 am
Triggers should help with what you are looking for.
November 26, 2002 at 5:30 pm
If you just want to occasionally look at the activity, perhaps Lumient Log explorer will work,
but if you have a real business/application need, then either use a table trigger, incorporate the logging into the insert procedure (you are using procedures to make the inserts, right?), or make it part of the transaction you use to insert or update the record.
All logging should go to another table, ideally with a one-to-many relationship set up. You will also want a field that tells you which field was updated, so that you can track more than one type of field in the same table. But if the datatypes are different, you may need to adjust the approach. however, if you are using SQL2K, then you could use the variant datatype.
Or you could log the complete record, even fields that did not change, to a history/log table.
What's the business problem you're trying to solve?
November 26, 2002 at 6:09 pm
Another option is if you use SPs to control flow then place code in there on in you app to write this info to the database. But triggers guarantee an INSERT,DELETE,and UPDATE is not missed and you can see both old and new values.
And another is use Prfiler to catch to a table (if using SQL 2000) but won't get old and new values.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply