On Fri, 26 Mar 2004 11:21:32 -0000, "Keith" <@.> wrote:
Hi
I am developing an ASP application which will interact with a SQL database.
A requirement of the application is that there is a full audit trail of any
modifications to data.
I am struggling a bit to get my head round how to do this - just the
concept.
Should I create dummy tables, and when a users asks to modify something,
make a copy to the dummy table with date/time/user etc., or is there another
recommended way?
Sorry if this is a dumb question, but I have never built in an audit trail
before, and have specifically been asked on this occasion, that every single
field which is able to be modified by a user can be tracked.
One of the simplest methods I've seen is a journal that has three
fields. The Date/Time of a change, the user and the query string.
The journal is updated and then the query run. It's simplistic and
may not handle your needs but it worked pretty well for the system I
saw it on. You could use a trigger for this in SQL, the one I saw was
an Access database implementation.
Personally, I might be inclined to use SQL's auditing for this. SQL
Profiler controls this, and you can audit just about anything.
Auditing can be a performance hog so you need to decide what to audit
and watch it carefully, normally you'd set auditing on a separate SQL
box in a large environment. I've only used auditing to track specific
problem issues, not as a general audit trail.
Maybe the most common method is an auditing mechanism written in your
database. You log whatever events you deem needed, using triggers to
update the information. This is a write-it-yourself deal but there's
probably plenty of sample code around. Try a SQL group for
references.
Jeff