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

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

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

A question about looping

Hi All,

I would like to know the best way to approach the following requirement:

I have an ASP.net 2 web site which gets its data from SQL 2005.

I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing.

The rules process will fire when the details have been submitted to the database.

Table containg rules would contain something like:

ID, RuleSQL

1, (ClientAge >18)

2, (ClientIncome>10000)

3 Etc...

This a very simplified version of the table but gives the general idea.

I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data.

If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works.

any comments or suggestions would be welcomed.

Thanks,

Ian

Yes, you can definitely do what you are describing in the database!

FYI, if you need to embed single quotation marks in the sql statement string (that is surrounded by sinqle quotes, as in 'set status = 'done'', you have to use two sinqle quotes in a row to get one single quote in the sql statement output: 'set status = ''done'''. You keep the sinqle quotes on the outside of the string, and all single quotes inside the string need to be double single quotes. Don't use a single double quote. :)

declare @.sql_statement varchar(max)
declare @.table_name varchar(256)
declare @.rule_where_clause varchar(max)

declare rule_csr cursor FAST_FORWARD for
select table_name, rule_where_clause
from the_rules
order by table_name, rule_where_clause
for read only

open rule_csr

fetch nextfrom rule_csrinto @.table_name, @.rule_where_clause

while(@.@.fetch_status<>-1)
begin

set @.sql_statement = 'Update ' + @.table_name + ' set status = -1 where ' + @.rule_where_clause

exec (@.sql_statement)

fetch nextfrom rule_csrinto @.table_name, @.rule_where_clause

end

close rule_csr
deallocate rule_csr

|||

Forgot to add this warning.

When possible, avoid using cursors because they are relatively slow compared with re-structuring your sql commands to make proper use of set-based data manipulation (as opposed to row-based data manipulation). In this case, you are probably stuck with using a cursor.

A littel help with table design....

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

SimonPersonally I'd do that logic in the objects rather than the DB. But if you want to use the DB then you need to create an Audit Table(s). Then use a trigger to write the values and a timestamp into the Autit table whenever the value changes.|||I need to do the similar thing.
I created second database as the log for the main one. It has all the table as the main one, and with additional fields to save userId, updata type, update date/time, etc.
Trigers are added to the main tables to insert old value into log database.
I appreciate it if anybody in this forum has better idea.