Need Expert Help for this situation

[bungpeng]

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...

[a_jam_sandwich]

if your using SQL server yes

 

Via stored procedures and triggers

 

Andy

[bungpeng]

I can't use store procedures, because my application is database indenpendent (user can choose any database they want)

[Nerseus]

I assume you're writing all the SQL by hand - you're 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, it's up to you to decide what you want. I'd 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

[bungpeng]

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

 

We going to store everything as long as database's record was changed

[quwiltw]

My opinion is that auditing outside of the database is not worth much.

 

To do it I suppose you'd 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).

[Heiko]

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 version's key and the new version's 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

[bungpeng]

quwiltw:

Item of datarow? is it datarow is a class? or property?

 

Heiko:

I not really get what you mean.... any simple example?

[Heiko]

Hm. I thought that was a simple example....

 

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 ...

[quwiltw]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatarowclassitemtopic4.asp

Specifically,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatarowclassitemtopic4.asp

MyDataSet.MyDataTable.Rows(n) returns reference to datarow.

[bungpeng]

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

[quwiltw]

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

 

I must say I don't like the idea of keeping the audit records and the "current" records in the same table though. I think I'd be inclined to create a mirror of each audited table and put the audit records there. (of course, I'm no dba, I just pretend sometimes on our smaller projects).

[Heiko]

Yes, they do.

 

It's not much of a problem here, because there's 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.

[bungpeng]

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....

[Nerseus]

Another consideration is how often/available the historical data needs to be seen. If you don't 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, it's a manual process that involves someone with database knowledge to extract the data. It works out well in some instances, where you don't 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

[quwiltw]

Not that I've done this before, but just picking up on that thought, if the audit data doesn't 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 wouldn't be "easy" to peice things back together but doable.