10-27-05, 11:51 #1Registered User
- Join Date
- Aug 2005
Unanswered: Old & New values for my Activity Log ??
I have a requirment to create a activity log to log all changes that
take place in my tables as they happen. All my Inserts,updates & Deletes take place inside stored procs. A windows .NET application calls the stored procs and passes the data set to the stored procs to execute them on a Sql server 2000 database.
When an update statement is run inside my stored proc, the dataset only passes me the new values. but,i will need the old value from the database before the update takes place to report in my activity log. What is the best way to do it ?
My activity log shold report a description like this
"Name Changed from 'Robert Johnson'(old value) to 'Bob Johnson'(New Value)"
Though i can do a select from the tables to get the old values and match the new values to get the differences before running the update statements, it is too much work as i have well over 100 stored procs in my application and i have to check for every single field value in a table. If only one column changed in a table containg 20 columns, i would still have to check for 20 columns before determining which fields changed.
Can any one suggest me a better solution to report the old values and new values for all the updates that take place in a database through stored Procedures.
10-27-05, 13:54 #29th inning DBA
- Join Date
- Jan 2004
- In a large office with bad lighting
Looks like you will have to use an AFTER trigger on the update to capture the data in the inserted and deleted tables and write them to history tables (one for each table that ** all ** procs could possibly be updated in the application.
If you need to capture new and deleted data, those will be INSERT and DELETE triggers as well. Looks like the inmates have taken over the asylum because your database is going to grow and grow and grow unless you put some sort of archive process in place to move those histories to off-disk storage.
Don't forget to write your triggers to handle record sets instead of assuming that single row operations will take place all the time. It is a wise man that plans for the actions of idiots ... or stated another way ... "Murphy was an optimist!"
-- This is all just a Figment of my Imagination --