Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: Help: Turning an audit table into a joinable table

    Hey there,

    I have an audit table of actions done to customer records

    table_name column_name table_identity old_value new_value audit_time exchange_manager_id TNM_table_audit_id
    customer pos_type_id 2 1 16 2009-03-25 14:54:37.133 46 71151
    customer pos_type_id 2 1 16 2009-03-25 14:54:37.133 46 71152
    customer pos_type_id 2 16 1 2009-03-25 14:54:49.227 46 71154

    Each time a record is changed it makes a row for table name, column name, table identity the old value, and the new value and who did the changing

    I can run a where clause now to just get the audit of the values I want but want to translate the data.

    so

    what i want to do is put the values into a flat table so I can run join off that to bring back what values each customer had over a specific range of time.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This kind of audit table is one way of storing audit data. The advantage is that you only need one audit table for all your tables and that it may be space efficient by only keeping audit data for the columns you are really interested in.

    I store the whole record in my audit tables. The advantage is that you have all data easily available. The disadvantage is that you need a separate audit table per table you want to monitor. When you have big records, the audit table may use a lot of disk space.
    My triggers fire after each INSERT, UPDATE and DELETE. I also store the event that made the change, C(reate), U(pdate), D(elete). The first audit record is the INSERTed record, the next are the UPDATEd records and the last one is the DELETEd one (when there have been UPDATEs and DELETEs on that record),
    When I display the data for one particular Id, I have code that checks for different values between equal columns in consecutive records and display those in a different colour (using CSS).
    I also store the datetime of the event. If you also add a column that holds the datetime of the next event, you can use those two to select all the data that was in your database at a particular moment ... WHERE dateTimeOfInterest >= datetimeEvent AND dateTimeOfInterest < datetimeNextEvent. Updating the datetimeNextEvent column of the previous audit record is not done at trigger time, but later, when I need to use the audit table.

    Code:
    table_name	column_name	tbleId	old_val	new_val	audit_time	exchange_manager_id	TNM_table_audit_id
    customer	pos_type_id	2	1	16	2009-03-25 14:54:37.133 46 71151
    customer	pos_type_id	2	1	16	2009-03-25 14:54:37.133 46 71152
    customer	pos_type_id	2	16	1	2009-03-25 14:54:49.227 46 71154
    When I look at your sample data, you only store data of one column of that table.

    I tried to solve it, but I stumbled upon some problems:
    - how do you know what the initial values in that record were (the initial values after the INSERT)?
    - are columns that were not updated also stored? (normally they are not)
    - how to you know if multiple records in the audit table were all altered in one event? Like with UPDATE MyTAble SET col1 = .., col2 = .., col 14 = .., col25 = .. Perhaps you can use the audit_time, but I'm not sure if the resolution is high enough.

    When unaltered records were not stored, you will have to make a program that will
    - reconstruct the initial record in memory (based on the oldest audit data), INSERT it in the database,
    - apply all the changes to that record for only those columns that were altered in the next event, INSERT the resulting record,
    - apply all the changes to that record for only those columns that were altered in the next event, INSERT the resulting record,
    - and so on and on.
    Quite a task. Maybe there is another solution, but I wouldn't know how to do it. The problem is that you need to know the previous values of all the columns that were not altered. What if important columns were not stored in your audit table?

    If you are planning to use the audit data like you describe, I would convert to storing the full record in the audit table. It will save you the time to write and debug that program, perhaps at the cost of some extra hard disk space.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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