: 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:
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
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.
infidel
$ select * from users where clue > 0
no rows returned
