[AccessD] Audit Trail Suggestions

[McGillivray, Don]

Well somewhere in the bowels of the application layer is a function or some such that parses the nasty string so the application can display the history, but it's available for use only within the context of the application.  For querying directly against the database, I built a function in Oracle that parses the string and optionally allows me to specify a target column, old value, and/or new value.  That way if I'm looking for audit records where the value of column x from table y changed from a to z, or from a to any other value, etc., I can pass the string to the function with the appropriate parameters and select those rows where a value is returned by the function.  It's crazy expensive, and I hate using it.  Thankfully, we don't have many requests for data that require me to interrogate that string.

Not my design, and like I said - a total PITA, but possibly useful in some contexts.  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Monday, March 16, 2015 12:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Audit Trail Suggestions

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

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