Need suggestions on datatypes... 1

[bessebo]

I am developing an auditing scenario and what I envisioned was creating an audit trail table that would track any changes that were made to specific fields in a table that is in our production database by use of triggers. I thought it would be cool to assign an audit id to this audit so that I could add more audits later should other users want to track changes in other tables. In the actual table that tracks the changes I want to have the old_value, new_value, etc. My problem is that since I want to make this generic the old_value and new value could either be int, float, varchar, etc fields.
I guess what I could do is create multiple fields, one called old_value_int, new_value_int, old_value_float, new_value_float, old_value_varchar, new_value_varchar. Then I could also create a field (let's call it fieldtype) that would identify the data type so that when I report off of this table if the fieldtype is integer I would write out the old_value_int and new_value_int.
Then I started thinking that it would be great if there was say a generic data type that I could write all of the old and new value fields to and just convert everything to character strings in the report. Any suggestions?

 

[RiverGuy]

I take it to mean that you plan on writing records for each individual field that has changed as opposed to writing out a representation of the updated row before and after? In that case, I would have your FieldType field-and I would put the old and new values in a large varchar field. That way, you'll be able to cast the values back if you need to do math on them for some reason.

 

[ptheriault]

I've just had to spend the last 2 months trying to undo an audit table like the one you are describing. The table looked like this.

column changed old value new valued

All fields were varchar(4000)

It was a mess. The only way to get to the data was to write a cursor. Performance againt the audit table was very poor. I would not write an audit log this way.

I would make a group of tables that mirror your primary table. For example if you have a personal table. I might make a table name log_personal. I would write a record to the log table everytime you make a change to the personal table. This way you always have a complete view of each record without having to join back to the transactional data. In fact you might go as so far to make an audit database.

Also, you should not use Float and real datatypes. They are subject to rouding errors when dealing with decimal values.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

Thanks for the info. I appreciate your suggestions and will have to make a decision on with method I will use. I appreciate your experienced insight.

 

[SQLSister]

We have an audit database. old and new fields are stored as ntext. This way they can accommodate just about any type of data that could be used.
We have a separate table for each table we are auditing (these tables get really huge) and each record includes the id field from the table (stored like 'person_id = 99000') and the fieldname of the field being audited and the old and new values and a transaction_id. We also have a separate table which records the date and time of the transaction which gives us the abilty to group transactions which were made in a batch together making it easier to find them and undo them if need be. This is the table which also records who made the change.

triggers of course populate the audit tables.

This structure can be hard to get data out of until you get used to it. I've finally gotten the hang of it but it took awhile.

Advatages are:
easy to set up auditing on a new table as all audit tables use the exact same structure and the triggers would only need the table name changed.

It never fails because of a change of datatype or the addition of a new field.

Once you learn how to extract data from it (which can be complicated) it is relatively easy to research a problem that happened. It is a bit harder to undo the problem, but again once you have written the code to do so once, it is easy to adjust the code for a different situation.



Questions about posting. See faq183-874

 

[bessebo]

Thanks for your input. I like the way that you use ntext fields so that you can just include changedate, oldfield, newfield, etc. I may try that method. Can you elaborate a bit on "extracting data from it (which can be complicated)"?

Thanks,
Bessebo

 

[scorpio66]

bessebo, follow ptheriaults advice - honestly, it's the best way of auditing.

Where I'm working we have TBL_RAW, TBL_HIST and a view TBL.

TBL_RAW contains the base data, TBL_HIST the historic data. However, for this audit to work correctly you must never physically delete data. Therefore, in all tables we have a status field which we use to logically delete records. TBL is a view that only selects valid data from TBL_RAW.

Periodically we hive off a chunk of data from TBL_HIST to an archive but we can easily track whatever changes have occured.

 

[scorpio66]

Oh, one more thing.

If the definition of TBL is

TBL_ID int identity,
field1 datatype,
field2 datatype,
field3 datatype,
...
status char(1)

the definition of TBL_HIST we use is

TBL_HIST_ID int identity,
TBL_ID int,
field1 datatype,
field2 datatype,
field3 datatype,
...
status char(1)

You can put in a foreign key constraint for TBL_ID in the HIST table if you want.

 

[bessebo]

Thanks for your input. I appreciate it...