Results 1 to 7 of 7

Thread: Audit Data

  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Unanswered: Audit Data

    Hi all,
    I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

    AuditTable that have the following fields:
    AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

    then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

    Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
    Thanks,

  2. #2
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    What do you need this for?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One method is to use the Binary_Checksum() function across both the original record and the updated record. If the values returned are different, then the record has changed and you should copy the original into your archive table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2005
    Posts
    6
    Does that mean i need to check every single field? that will take a while isn't it? is there any better way? how do you guys do your audit?

    The reason I need this for is to keep a log on who did what on when on selected tables in a given DB, where sometimes ppl will ask things like who delete this record, or who update that record etc, and i can't just keep the last update as they need to chase back who did updates, for example PersonA update 2 times and PersonB comes in and update 3 times then PersonD delete that record...i need to keep track of all these info.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are many off-the-shelf packages that will give you this functionality. Download the eval and see how it is done. Most likely it all bottles down to C2-based auditing trace.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    I ran across this the other day. The VB package builds a shadow audit table (per auditied table) and builds the triggers for insert, update and delete. Also has paramaters which build SP's to trim the audit records or restore from the audit table.

    http://www.gotdotnet.com/Community/U...5-38313ce31166
    Fred Prose

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you are going to have problems with audit table design:

    "AuditTable that have the following fields:
    AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate"

    I suspect you will find it very difficult to manage or retrieve usefull information from a single audit table that covers all the tables in your database, though at first glance that may appear to be the simplest solution. You will find that writing queries against this to track changes or restore lost data will be quite cumbersome.

    A more common approach is to create a copy of each of the tables you want to archive, perhaps with "_HIST" appended to the name and with your Update time columns included as well. Then just have an update trigger on your production tables that copies the entire record to its history table.

    Even though you are increasing the number of tables, you will find this method much easier to use.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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