Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Tracking Field History

I was asked to do a new application where they would like to keep track of each change for each field. For example if the address changes for a customer they want to know when the change was made and what the old address was. And this could happen numerous times for each field. Does anyone have any resources that I could look at to find the best way of doing this?? I would like to know the best database structure. I have a few ideas but not sure what may work easiest. Any help would be great!!

Thanks

Comments

  • infidelinfidel Posts: 2,900Member
    : I was asked to do a new application where they would like to keep track of each change for each field. For example if the address changes for a customer they want to know when the change was made and what the old address was. And this could happen numerous times for each field. Does anyone have any resources that I could look at to find the best way of doing this?? I would like to know the best database structure. I have a few ideas but not sure what may work easiest. Any help would be great!!

    Simplest solution I know of is to create an audit table that matches the table you want to keep track of. For example, if you have an ADDRESS table, then create an ADDRESS_AUDIT table (or whatever name you wish). The audit table has all of the fields as the main table, plus DML_DATETIME, DML_USERID, DML_TYPE. Then you put a trigger on the ADDRESS table to fire on every insert, update, or delete. When the trigger fires it adds a record to the audit table with the system date, the user id of the person making the change and whether it was an INSERT, UPDATE, or DELETE.

    This is the simplest approach, but for a table that gets updated a lot is going to produce a huge audit table.

    The next easiest option (which is what we currently use) is to create a single audit table for the entire database. Then your triggers (one for each table you want to keep a trail of) only look for inserts, updates, or deletes of the specific fields we are interested in watching. Our audit table then stores information kind of like:
    [code]
    TABLE_NAME RECORD_ID COLUMN_NAME OLD_VALUE NEW_VALUE DATE_TIME
    ------------------------------------------------------------------------
    ADDRESS 101010101 CITY (NULL) PHOENIX 1/1/01 1:01:01
    ADDRESS 101010101 CITY PHOENIX TUCSON 2/2/02 2:02:02
    [/code]

    And so on and so forth. An OLD_VALUE of NULL means a value was inserted into that field. A NEW_VALUE of NULL means a value was deleted from that field. (note that doesn't necessarily mean in either case the whole record was inserted or deleted)

    You'll have to make sure they really want to audit every change to every field - and to make sure they've got enough storage to handle it.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.