By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,319 Members | 2,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,319 IT Pros & Developers. It's quick & easy.

Audit Trails and SQL

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jul 19 '05 #2

P: n/a
> A requirement of the application is that there is a full audit trail of
any
modifications to data.


http://www.aspfaq.com/2496
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.