A question on ow to design some tables

Hi all,
I have a fairly tricky problem that I'm not sure how to approach.
I'm making a web application that manages drug trials. One of the
requirements of the system is if anyone makes changes to a field, the old
value and the new value need to be stored, along with the time of the change
and the reason for the change
The problem is I don't know how to support this for all the various fields
in all the various tables.
For example I have tables for storing basic patient details and then tables
for storing data on patient visits, patient screening data and so on.
Can anyone suggest how I could make a table or tables to store this audit
data for all the fields in all the tables? I'm not sure how to do it!
:-(
Thanks to anyone who can help
Simon
Hi Julie,
Thanks for your reply.
What I'm really stuck on is how to arrange the audit tables so that they can
store all sorts of information from the different types of data from all the
tables
Do you have any ideas along those lines?
Thanks again for your help!
Simon
|||What if you make duplicate tables and append "_archive" or similar to their
names, and use triggers to copy the original record in it's entirety to
these archive tables before inserting the new data into the main table?
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:eexDr4VHEHA.2260@.TK2MSFTNGP09.phx.gbl...
> Hi Julie,
> Thanks for your reply.
> What I'm really stuck on is how to arrange the audit tables so that they
can
> store all sorts of information from the different types of data from all
the
> tables
> Do you have any ideas along those lines?
> Thanks again for your help!
> Simon
>
|||Hi Simon,
Keith has really answered the question. The best way I
have found how to do this is for every table you want to
audit, create an audit table.
In the example given to you two tables tblTesting is the
table the auditing is to take place, and tblTestAudit is
table where the changes will stored. You can cut paste and
run the code in Query Analyser and try it out if you want.
The example will capture every change made in the
tblTesting database and put the before and after values in
tblTestAudit.
What I actually showed you was quite basic, you can expand
it to show the name of the user who made the change, date
time of change, infact anything that can be programmed in.
So to recap.
1. The best way I have found is for each table you will to
audit create an audit table.
2. Cut and paste the demo, execute it in Query Analyser
and see what it does
3. Figure out what other things you need to change it.
Enjoy
J

>--Original Message--
>Hi Julie,
>Thanks for your reply.
>What I'm really stuck on is how to arrange the audit
tables so that they can
>store all sorts of information from the different types
of data from all the
>tables
>Do you have any ideas along those lines?
>Thanks again for your help!
>Simon
>
>.
>
|||Many thanks to both of you!
:-)
Simon