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 little help on a proc

I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.comDavid
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This is great, but how do I now access that recordset if it exists?
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23hYr9xRDFHA.1524@.TK2
MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This should get you started:
create proc foo as
@.loginname varchar(20),
@.password varchar(50)
as
--verify that the login is valid
if not exists (select * from sometable where somelogincolumn = @.loginname)
BEGIN
RETURN (1)
END
--verify that the password is valid
if not exists (select * from sometable where somelogincolumn = @.loginname
AND somepasswordcolumn = @.password)
BEGIN
RETURN (2)
END
select somecolumn
from sometable
where somelogincolumn = @.loginnamae
and somepasswordcolumn = @.password
RETURN (0)
GO
The return code will be 1 if the login does not exist, 2 if the password is
not correct. If the login exists and the password is correct the third
statement will be executed and the return code will be set to 0.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a
record set, look at me go. However, I need to get a little more advanced. I
need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||So the best process is to rerun the query? Wouldnt that get extensive for
the server? Can I run something like this:
create proc users
@.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
Run query that verifies username
if username valid
check password against previously returned recordset
if password valid
@.fullname = returned data, @.security, etc.
else
return 2
else
return 1
end
By the way, i'm accessing this from an ASP.NET page.
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> This should get you started:
> create proc foo as
> @.loginname varchar(20),
> @.password varchar(50)
> as
> --verify that the login is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname)
> BEGIN
> RETURN (1)
> END
> --verify that the password is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname
> AND somepasswordcolumn = @.password)
> BEGIN
> RETURN (2)
> END
> select somecolumn
> from sometable
> where somelogincolumn = @.loginnamae
> and somepasswordcolumn = @.password
> RETURN (0)
> GO
> The return code will be 1 if the login does not exist, 2 if the password
> is
> not correct. If the login exists and the password is correct the third
> statement will be executed and the return code will be set to 0.
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> I'm fairly new to the stored procedure world, I can create one to return a
> record set, look at me go. However, I need to get a little more advanced.
> I
> need to do the following in a proc:
> Query a honken huge query and check if login is valid. If so, continue
> through proc and return the necessary data, if not return an invalid
> status.
> Thanks a million!!
>
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>|||You would change the DO SOMETHING into a proper select statement that
returns the data that you are interested in.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:ubZno6RDFHA.936@.TK2MSFTNGP12.phx.gbl...
This is great, but how do I now access that recordset if it exists?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hYr9xRDFHA.1524@.TK2MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return
a record set, look at me go. However, I need to get a little more advanced.
I need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||You can do anything you want. Do you care if the loginname is invalid? Do
you care if the password is invalid? Or do you just want a "Yes,
authentication passed -- here is your data" or an "Authentication Failed"
notification?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> So the best process is to rerun the query? Wouldnt that get extensive for
> the server? Can I run something like this:
> create proc users
> @.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
> Run query that verifies username
> if username valid
> check password against previously returned recordset
> if password valid
> @.fullname = returned data, @.security, etc.
> else
> return 2
> else
> return 1
> end
>
> By the way, i'm accessing this from an ASP.NET page.
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
@.loginname)
@.loginname
a
advanced.
>|||I guess, I'm confuzzled about how to access the initial query throughout the
rest of the proc?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O3oE6RSDFHA.3452@.TK2MSFTNGP09.phx.gbl...
> You can do anything you want. Do you care if the loginname is invalid?
> Do
> you care if the password is invalid? Or do you just want a "Yes,
> authentication passed -- here is your data" or an "Authentication Failed"
> notification?
>
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> @.loginname)
> @.loginname
> a
> advanced.
>|||I am why you think you need to access the initial query throughout
the rest of the stored procedure.
It would be helpful if you told us what the requirements are for the stored
procedure. What are the inputs (parameters)? What are the expected results
(recordset/output variables/return code)?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I guess, I'm confuzzled about how to access the initial query throughout
the
> rest of the proc?|||Yes, that would me sense, I reposted at 11:12.
thanks!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e6HoWBTDFHA.392@.TK2MSFTNGP14.phx.gbl...
>I am why you think you need to access the initial query throughout
> the rest of the stored procedure.
> It would be helpful if you told us what the requirements are for the
> stored
> procedure. What are the inputs (parameters)? What are the expected
> results
> (recordset/output variables/return code)?
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> the
>

A little help moving my database

Hi everyone,
I need a little bit of help with the following.
I have a fairly small database at work that is contained in an SQL Server. I
want to get that database - inc tables, stored procedures and data on to my
SQL Server running at home.
I need to somehow get all the information onto a cd and take it home. But
I'm not sure how I should copy the database. Is it possible just to copy the
data files from the work computer and somehow import them into the target
SQL Server? Would that include the Stored Procedures?
Many thanks all
SimonRead into sp_detach_db and sp_attach_db. Provided you are using SQL
7/2000/MSDE, it will allow you to detach your database so you can bring the
.mdf, .ndf and .ldf files home and reattach them.
Note if you have SQL 7 at home, not only does the database have to be SQL7
format but the sort order of the source and destination server must be
identical, use sp_helpsort to find out. If you are SQL 2000/MSDE you are
gold.
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
This is the link to Microsoft's available product info download.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uD8MUYc5DHA.2572@.TK2MSFTNGP09.phx.gbl...
> Hi everyone,
> I need a little bit of help with the following.
> I have a fairly small database at work that is contained in an SQL Server.
I
> want to get that database - inc tables, stored procedures and data on to
my
> SQL Server running at home.
> I need to somehow get all the information onto a cd and take it home. But
> I'm not sure how I should copy the database. Is it possible just to copy
the
> data files from the work computer and somehow import them into the target
> SQL Server? Would that include the Stored Procedures?
> Many thanks all
> Simon
>

A little help moving my database

Hi everyone,
I need a little bit of help with the following.
I have a fairly small database at work that is contained in an SQL Server. I
want to get that database - inc tables, stored procedures and data on to my
SQL Server running at home.
I need to somehow get all the information onto a cd and take it home. But
I'm not sure how I should copy the database. Is it possible just to copy the
data files from the work computer and somehow import them into the target
SQL Server? Would that include the Stored Procedures?
Many thanks all
SimonRead into sp_detach_db and sp_attach_db. Provided you are using SQL
7/2000/MSDE, it will allow you to detach your database so you can bring the
.mdf, .ndf and .ldf files home and reattach them.
Note if you have SQL 7 at home, not only does the database have to be SQL7
format but the sort order of the source and destination server must be
identical, use sp_helpsort to find out. If you are SQL 2000/MSDE you are
gold.
http://www.microsoft.com/sql/techin.../2000/books.asp
This is the link to Microsoft's available product info download.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:uD8MUYc5DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi everyone,
> I need a little bit of help with the following.
> I have a fairly small database at work that is contained in an SQL Server.

---

I

quote:

---

> want to get that database - inc tables, stored procedures and data on to

---

my

quote:

---

> SQL Server running at home.
> I need to somehow get all the information onto a cd and take it home. But
> I'm not sure how I should copy the database. Is it possible just to copy

---

the

quote:

---

> data files from the work computer and somehow import them into the target
> SQL Server? Would that include the Stored Procedures?
> Many thanks all
> Simon
>

---

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.