[AccessD] Audit Trail Suggestions

[John W. Colby]

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

Uhhh yea.  So if you don't anticipate ever needing to query the change 
log then this might work.  The issue becomes if you want to find a 
change "sometime this last year in field xyz of table K". How do you do 
that?

The strategy I used was to build a table of table names, a child table 
of field names and then a log of FldID (contained 
TblID)/RecID/UserID/DteTime/OldVal/NewVal/DataType.  That strategy 
allowed easy querying of any of the various major IDs, plus the values etc.

The system used a cache of the table/field info and added new tables / 
fields as new ones were encountered.  Thus is a field was never edited, 
then it never cluttered up the system.  In the systems that implemented 
this, specific fields were "hot" and edited a lot.  Most fields were 
cold and rarely / never edited.

But yea, actually trying to get data back out of a denormalized "string" 
system would be pretty darned difficult.

John W. Colby

On 3/16/2015 2:47 PM, David Emerson wrote:
> Thanks Don for the suggestion.  In my case I am not expecting too many
> changes so a single record per field will suffice.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> McGillivray, Don
> Sent: Tuesday, 17 March 2015 4:51 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Audit Trail Suggestions
>
> 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
>