Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012

    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?

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    What version of sql server are you using? CDC might work for you.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    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.

    blindman "sqlblindman"

  4. #4
    Join Date
    Oct 2009
    221B Baker St.
    I keep running into problems that are beyond my ability.
    Clarify which problems?

    Show sql you tried that does not work and the error(s) generated or the unwanted data returned.

    It will help if you post some sample data rows and what you want returned from your query.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts