How to keep track of changes to the table 1

[Andrzejek]

I have a table in my data base where I keep some Customer information, standard stuff like ID, Name, Contact, Address, etc. Now we need to keep track of When, Who and What had changed to this table. I guess there were some problems with people changing info and now some people what to know who did it in case something wrong was entered. Customers are pretty study, so no new ones added and no deleting. So I have another (Log) table with fields like: When, Who, ID, FieldName, OldInfo, and NewInfo. And I have some logic to write any changes that are made to my Customer table, and this Log table will be used to generate a report with any and all changes to the Customer table. When, Who, and ID is not a problem, but in my FieldName I have a name of the Field in my Customer table that – as you know – may not mean anything to my users. Also, in my OldInfo and Newinfo fields I end up with information like 1 and 3 which are in yet another (look up) table, so 1 and 3 have some meaning to me and to the program, but not much to my users.

So I need to re-think and re-do my approach to the Log table. Instead of FieldName (from my Customer table) I need something more descriptive. And instead 1 (in OldIfo) and 3 (in NewInfo) and need to have information from my look-up table, like 1 is ‘Mr.’ and 3 is ‘Miss’, so that’s the information I need to write to my Log table. I am thinking about writing to my Log table what users actually see on the Form. I usually have a label describing the field, something like:
[tt][blue]
Address1:[/blue] [123 Main Street][/tt]

Where Address1 is a label named lblAddress1 and [123 Main Street] is a text box named txtAddress1. There also could be combination of a label and a drop-down combo: lblArea and cboArea, for example.

I think I may want to loop thru my controls on the Form, remember what was in text boxes and drop-down combos, and compare it to values in these controls when Update command button was clicked, and detect the changes and write them into the Log table. I just don’t know if that’s the best approach. So instead of re-inventing the wheel, I thought I would ask here how this is done by other people.

Any suggestions?

VB6 with Oracle

Have fun.

---- Andy

 

[gmmastros]

I am doing something very similar in my database. Instead of approaching this from the application, I would strongly encourage you to let the database do the heavy lifting. I am no expert with regards to Oracle, but I do know that they support triggers. With Microsoft SQL Server, you can have Insert, Update, and Delete triggers. You can also have the triggers fire before the action or after. You also have access to the original data in the table and the new data.

In my database, I have triggers on almost every table so I don't need to keep track of which table was changed. The log table for a customer table would be named Audit_Customer. The triggers I have compare the original data with the new data and adds a row to the audit table only if the data has changed.

I also added another table to the database that stores "friendly" column names. So Customer_Salutation would simply be salutation. This table looks something like this:

[tt]
Table_Name Column_Name Friendly_Name
---------- ----------- -------------
Customer Customer_Salutation Salutation
[/tt]

I don't worry about storing lookup values in the log table. Instead, I wrote reports that do all the proper linking and also shows the "friendly" column names.

It was a fair amount of work to set up, but it works really well. The advantage to doing this in the database is that you no longer need to worry about what caused a change because the database will catch them all. As long as you write your trigger code in a way that supports multiple row updates, and your reports generate the correct output, you should be fine. If you allow data to get imported in a bulk way, the triggers will still fire. If someone is able to update the data outside your application, the triggers will still fire. If you later write a web interface (instead of VB6), your triggers will still fire. All of this adds up to a compelling argument for implementing this functionality in the database.

I was originally concerned that the auditing would slow down the database, but it hasn't. Many of my customers are using SQL Express which is free but has a size limitation. Because of this, I added functionality that allows them to delete older auditing data. This is only to accommodate potential size problems.

Like I said, I don't know Oracle very well, but I am 100% confident that you can do all these things with it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

I see your points and I like them all. I can see the benefits of "friendly" column names, too. I would like to know more about your "reports that do all the proper linking" - most of our reports are done in Excel called from our VB 6 application, that's where the "proper linking" would happen, I just don't know how to do it (yet...)

Have fun.

---- Andy

 

[strongm]

Oracle (and SQL Server) both have built-in auditing capabilities, you know.

For an overview of Oracle's stuff have a look here as a starter:

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

 

[gmmastros]

I haven't confirmed this, but I don't think the express edition of SQL Server has the built-in auditing functionality.

All of my audit tables have:

The primary key column(s) of the original table.
[tt]OriginalValue (string)
NewValue (string)
ColumnName
UserName
DateTimeStamp
ChangeType[/tt]

Note that the original and new values are always stored as string (varchar) because the column could be an int, a date/time, or an actual string.

For example, if I change the EyeColorId for a contact, you might see something like:

12345,1, 2, EyeColorId, gmmastros, 2011-12-27 14:12:23.345, "U"

So... the EyeColorId for contact 12345 changed from 1 to 2. In this case, I would have an EyeColor table that has id and description, like this:

1 Brown
2 Blue
3 Hazel

I then created a report that links the Audit_Contact table to the EyeColor table. There's actually 2 links to the EyeColor table, one for the Original value and another link for the new value. I only do the link if the column name is EyeColorID. Sometimes there is no linking table when the data is simple like a name or description.

There are several reasons for doing it this way.

1. This allows the trigger to be simpler, and simpler usually means faster.
2. This allows for less storage space.

I could have written the trigger so that it pulls all the data I need for the report, but this would "cost" more in terms of time and storage space. For example, I could have written the trigger so that the log table would look like this:

"Mr. Some Name", "Brown", "Blue", "Eye Color", "gmmastros", 2011-12-27 14:12:23.345, "U"

In my experience, users want auditing, but they only use it when they have a reason to. So I am less concerned about the performance of the auditing reports and more concerned about the storage speed and trigger performance.

Note that the Change Type is a char(1) and can contain the values "I", "U" and "D" which corresponds to Insert, Update, and Delete.

It's not a perfect solution, but it suits me well.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

I already keep information like yours in my Log table:

12345,1, 2, EyeColorId, gmmastros, 2011-12-27 14:12:23.345, "U"

Except I don’t keep “U”pdates, since I will not have any “I”nserts or “D”eletes, all of mine will be Updates. So I just need to keep somewhere that EyeColorId corresponds to a look-up table XYZ and with what I have I can create a report for users to have. Great.

And thank you.

Have fun.

---- Andy

 

[strongm]

>I don't think the express edition of SQL Server has the built-in auditing functionality

Didn't realise we were talking about SQL Server Express; OP mentioned Oracle.

For Oracle, if I was implementing auditing I'd use the built-in tools (far better performance than triggers), probaly use fine grained auditing to further reduce overheads, and check out whether flashback querying is enabled. Why reinvent the wheel?

 

[gmmastros]

Strongm,

I mentioned SQL Express as a reason to justify why I implemented auditing with triggers. For me (and my customers) it's the right choice.

If I had the luxury of using built-in auditing, I would jump on it. Regardless, my original advice to implement auditing in the server was still the best advice I could give. How it is implemented would depend on the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom