Unanswered: Query off a Change-Log / Audit Table... HELP!
The assignment is to report what changes have been made to our database within a user-defined time range.
We've created an Audit-Log table that tracks all changes made to the database with the following columns;
Audit Date (When the change was made)
Table (What table was changed)
Action Name (Insert, Delete, UPdate)
KEY_SK (Surrogate Key linked to different table's keys, dependent on what value is being changed.)
Column Name (The Value that has been changed)
Old , New Value (The changed value)
I keep running into problems that are beyond my ability. I've exhausted all of my own ideas and need help. (i'm a newbie to SQL).
So, does anyone have any suggestions?? I'd really like to hear how one would attack a query like this?
Unless you have very few data changes, my suggestion would be to use a different method of storing your Audit Log.
You're going to end up creating a record for every column in every table that is updated.
Update 1000 records in a table with 50 columns: 50,000 audit records.
If it's not practically useful, then it's practically useless.