Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Lightbulb Unanswered: Which is better for an audit trail

    A table that stores all [updated | deleted] transactions in a database
    i.e.
    TableName, TableId, ColumnName, ValueType, NewValue, OldValue, DateChanged

    or

    A copy of each individual table, which could add up to a lot of tables

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on just what you want to know about the changes. My guess would be that the audit table would provide more usable information, and the table copies would be easier to do and simpler to explain.

    One option that you've missed is to use SQL Profiler to record whatever information you need. This consumes ENORMOUS amounts of disk, but it allows you to track every change to the table, without exceptions, in nausiating detail if you wish.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I've only ever implemented auditing (using triggers) for selected tables in a database; I've never tried it for all the tables.

    That said, I would think that the former solution (one table) might be a source for contention as an update to a single row on one table appears to require multiple inserts into the table (one for each column being changed). It would also seem to be a bit of a nightmare to keep up with changes and updates.

    I am seeing more and more references to using Lumigent's products for doing auditing. But that could be just an advertising push. Since I've never used their products, I don't have an informed opinion about them.

    Personally, I think I would choose the latter option, but I would be as selective as possible with respect to which tables need to be audited.

    regards,

    hmscott

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I like having history tables. It makes archiving and warehousing off the tables easier. It also keeps the OLTP system thin and fast where you can design to look at current data.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    the best thing for an audit trail is Lumigent Entegra and for minimal auditing Lumigent Log Explorer

Posting Permissions

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