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
SimonHi Simon,
There is an in built database utility called a Trigger,
these perform an automatic response for an INSERT, UPDATE
or DELETE.
You can access Triggers in EA by clicking on the 'Design'
of a table in EA, its the button next to primary key.
Anyway in the following example I have created three
triggers in a database table which stores the before and
after values in an audit table.
Give it a try and see if its what you want, you may also
want to read up about triggers on BOL.
J
CREATE TABLE [dbo].[tblTestAudit] (
[Type] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] NULL ,
[OldVal] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[NewVal] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblTesting] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Testing] [char] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTesting] WITH NOCHECK ADD
CONSTRAINT [PK_tblTesting] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE TRIGGER tk_INSERT ON [dbo].[tblTesting]
FOR INSERT
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.NEW as char(20)
select @.ID = ID, @.NEW = Testing from inserted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('INSERT', @.ID, '', @.NEW)
GO
CREATE TRIGGER tk_DELETE ON [dbo].[tblTesting]
FOR DELETE
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.OLD as char(20)
select @.ID = ID, @.OLD = Testing from deleted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('DELETE', @.ID, '@.OLD', '')
GO
CREATE TRIGGER tk_UPDATE ON [dbo].[tblTesting]
FOR UPDATE
AS
DECLARE @.TKTYPE as char(6)
DECLARE @.ID as int
DECLARE @.NEW as char(20)
DECLARE @.OLD as char(20)
select @.OLD = Testing from deleted
select @.ID = ID, @.NEW = Testing from inserted
insert into tblTestAudit (Type, ID, OldVal, NewVal) Values
('DELETE', @.ID, '@.OLD', '@.NEW')
>--Original Message--
>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