[AccessD] Audit Trail Suggestions

McGillivray, Don DMcGillivray at ctc.ca.gov
Mon Mar 16 10:50:45 CDT 2015


A little late to the party, but thought I'd chime in with an approach that I've encountered.

At my current job we use an off-the-shelf product based on an Oracle backend.  The application layer implements optional audit tracking, selectable at a data entity level.  But instead of writing a record to the audit table for every single column value change, it summarizes all the changes that occurred in each update event, concatenating the results into a specially delimited string.  Separate fields capture the ID of the affected record, the user ID, and date, but all the change information is embedded in a single (admittedly hideous) value.  In the case of our application, each value in the string is preceded by an asterisk followed by a value indicating the length of the value to follow, followed by the value itself, with column names, old values, and new values presented in pairs in a predictable sequence.  Something like (air code here):

*#ColumnName1*#ColumnName2*#OldValue1*#OldValue2*#NewValue1*#NewValue2

There may be some other data at the head of the string, such as column count or some such, but you get the idea.

This strategy represents a shift from a one column/one record approach that was originally used with the product, and presumably was implemented to reduce the number of records required to house audit data in large scale implementations.  If you anticipate a large volume of audit data as the result of your audit tracking, it may be worth considering a similar strategy.  Of course, you'd need to build the secret decoder ring to translate the arcane delimited string into something human readable, and there's processing overhead associated with that, and I'll tell you from experience that writing useful queries against that delimited string is a total PITA.  Still, folks a lot smarter than me came up with the idea, so something like it may be worth considering, depending on the circumstances.  



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, March 13, 2015 9:22 PM
To: AccessD
Subject: [AccessD] Audit Trail Suggestions

Hi Team,

Looking for ideas for implementation.

Access 2010 FE, SQL 2008 R2

A client wants to keep track of some fields in some tables when they are changed.  There are over 50 tables involved and anywhere from 3 to 30 fields per table.  The tables and fields are predefined.  Most of the data changes will be done via Access screens but there are some fields that are changed through code

When a field value is changed they want to store in a log table date, person making change, old value, new value.

Looking for ideas of how others might have tackled this type of problem before.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list