Need Expert Help for this situation

bungpeng

bungpeng

Well-known member
Joined
Sep 10, 2002
Messages
906
Location
Malaysia
I want to add a "Audit Log" feature in my existing application, it will keep all records before updated and after updated in database.

But I have not idea how it can be done. For record after updated, we can retrieve from the Update SQL statement; but for record before update, we need to select the record and save to database first before updating process start.

In there any faster way to do it? for example: I pass the Update SQL statement in my AuditLog function, and It will do it all for me? It may possible but I think need many lines of codes...
 
I cant use store procedures, because my application is database indenpendent (user can choose any database they want)
 
I assume youre writing all the SQL by hand - youre going to have to add code (manually) to include extra INSERT commands to save off the changes. Since this will have to be done manually, its up to you to decide what you want. Id check with your clients to figure out excactly what they want stored. Some common options are only changed records but some may want the entire row saved off. More likely (hopefully), you may only have to audit/log certain tables... but this is a design issue, not a programming issue. Check with your clients before you touch any code :)

-Nerseus
 
Yes, this is design issue, so I need your opinions.

We going to store everything as long as databases record was changed
 
My opinion is that auditing outside of the database is not worth much.

To do it I suppose youd have to use the versioning of the dataset and manually iterate over the rows of the datatable doing two inserts for all added rows, an insert and an update for modified rows, and an insert and delete for deleted rows. I think the Item of a datarow lets you get at the different versions (Original/Current).
 
This is how we did it:

All Tables get a new field, indicating whether this record is "active" or an old version.

All Tables get primary keys in identical format (Varchar 36, for GUIDs)


We inserted a new table, containing keys for all database tables, plus an indicator, whether auditing is required or not.


Then comes another table, the real audit log:
Here we have a pointer to the table, the old versions key and the new versions key.

Then we programmed a central class to handle all auditing. This class also makes sure, that with every update a clone of the old version is created.

Hope this gives rough impression.

H
 
quwiltw:
Item of datarow? is it datarow is a class? or property?

Heiko:
I not really get what you mean.... any simple example?
 
Hm. I thought that was a simple example....

Code: 
Table NAM_Name.
NAM_GUID as varchar(36)
NAM_FullName as varchar(255)
NAM_Archived as char(1) <-- can be "N" or "A", N = New, A = Archived
This is how all tables look.
Whenever a record is updated, do the following:
create a clone of the original record, but with a different GUID and
Archive Flag = "A". Store that record. Now update the original.

In the Auditlog, keep the table (NAM_Name in this case), and both GUIDs.

This allows you to recover all old versions, if required.



of course, make sure that all SELECT statements include a
WHERE XXX_Archive = N section ...
 
Heiko:
This this case the records of tables will grow very fast right? because all activities will keep at least one record. It is OK? Since you got experience with it.

quwiltw:
Thank you
 
Methinks in any case the tables will grow very fast, one of the unavoidable side effects of auditing.

I must say I dont like the idea of keeping the audit records and the "current" records in the same table though. I think Id be inclined to create a mirror of each audited table and put the audit records there. (of course, Im no dba, I just pretend sometimes on our smaller projects).
 
Yes, they do.

Its not much of a problem here, because theres not such an amount of update. However I understand your fears, that might very soon become very annoying in a high traffic environment, say a bank or a retailer or so.

*However*
I can not think of any way to keep an audit log that does not
require at least one database record per update. So, you will have to face that anyway, I suppose.
 
Thank again...

I think I have same opinion with quwiltw, but if separate the audit and real record in different tables, then we need double of tables in our database... this is also not.... just feel not perfect.... :) still looking for any good ideas....
 
Another consideration is how often/available the historical data needs to be seen. If you dont need to dynamically query the historical (audited/logged/etc.) data, then using something like XML might be an option. Simply store snapshots of data to a single table. We use this approach in some situations - in conjunction with a Database name, Table name, and primary key value (all of our tables use identity columns). If someone needs to view the historical data because of some issue, its a manual process that involves someone with database knowledge to extract the data. It works out well in some instances, where you dont need to query the changes.

Using the same table for historical data poses some problems, such as having to always find the most current or "active" record but is also a very common auditing approach.

Of course, any form of auditing is going to effectively double your database size (at a minimum) - either through double tables, or double data.

-Nerseus
 
Not that Ive done this before, but just picking up on that thought, if the audit data doesnt need to be immediately accessible, you could probably just log bulk dataadapter update operations but stuffing the entire "before" block of a DiffGram in an audit table. It wouldnt be "easy" to peice things back together but doable.
 
Back
Top