March 10, 2008 at 7:21 am
Hi,
Please help me in sorting out my Problem Providing me the solution .
My Problem is
I have a master table with Primary key on ID field (PatientID-(Patient-Table)) and it is an Identity field
And My child table has the same ID field(PatientID-(PatientDetails-Table)) and it has the relationship set
And the child table has its own Primary key of its own ID field(PatientdetailsID).
What I want is as soon as enter row of data into the master table (Patient-Table)and click save on my front end application(Which is ASP.Net web application)
I want to update Child Table’S (PatientDetails)ID field ( ie.,PaientID in the PatientDetailsTable) in the Child Table which relates the parent table ,by doing so I want to update the Primary key field (ie.,PatientDetailsID) & ForeignKey Field (PatientID)of the child table and to create row in the child table with two columns .(PatientID,&PatientDetailsID)
What I want to achieve is in my ASP.net Application as soon as I enter Master table
I want to Edit Child tables (about 15) one by one like a Wizard pages which will have The ID Field(PatientID) same in all my wizard pages .
I want to know whether I can incorporate triggers if so in which table (is it in Patient or PatientDetails) and I will be grateful If anyone gives the Script to-do so.I am also providing my two table sripts.
Sripts:
CREATE TABLE [dbo].[Patient](
[PatientID] [int] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NULL,
[UserID] [int] NULL,
[FirstName] [varchar](40) NOT NULL,
[Surname] [varchar](30) NOT NULL,
[DOB] [datetime] NULL,
[Age] AS (floor(datediff(day,[DOB],getdate())/(365.25))),
[Sex] [varchar](10) NULL,
[Occupation] [varchar](30) NULL,
[Ethinicity] [varchar](60) NULL,
[HomeTel] [varchar](15) NULL,
[Mobile] [varchar](15) NULL,
[Email] [varchar](40) NULL,
[AddressLine1] [varchar](30) NULL,
[Line2] [varchar](30) NULL,
[Line3] [varchar](30) NULL,
[City] [varchar](20) NULL,
[PostCode] [varchar](15) NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
[PatientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_User]
CREATE TABLE [dbo].[PatientDetails](
[PatientID] [int] NOT NULL,
[PatientDetID] [int] IDENTITY(1,1) NOT NULL,
[Date] [smalldatetime] NULL,
[NHSNumber] [varchar](12) NULL,
[HospitalRefID] [varchar](10) NULL,
[Ovaries] [varchar](15) NULL,
[ReportFromGP] [image] NULL,
[LMP] [datetime] NULL,
[DateStopped] [datetime] NULL,
[Comment] [varchar](150) NULL,
CONSTRAINT [PK_PatientDetails_1] PRIMARY KEY CLUSTERED
(
[PatientDetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PatientDetails] WITH CHECK ADD CONSTRAINT [FK_PatientDetails_Patient] FOREIGN KEY([PatientID])
REFERENCES [dbo].[Patient] ([PatientID])
GO
ALTER TABLE [dbo].[PatientDetails] CHECK CONSTRAINT [FK_PatientDetails_Patient]
I want to incorporate this through database level .
I am using SQL Server2005-Express
Although Iam using ASP.net C# I am new and I will not be able to do this in my front end.
Please help me wth the solution.
Thanks
rameshs_2000
March 10, 2008 at 7:27 am
I'm not sure you understand databases, so apologies if this seems too basic.
You don't need a child row for a parent row. Typically you insert a parent row, get back it's identity, then use that to insert or update child rows. With the parent ID returned to your app, you can then decide as you go to each wizard page, if you have data to insert (by selecting from the child table) and then you also know whether to insert or update.
You could let a stored procedure determine if it should be an update or insert, but you have to think through the logic of your business application and determine if there are criteria you can use.
March 10, 2008 at 7:58 am
Hi Steve Jones,
Thanks for your Quick Reply.
It really helped me And Iam now Looking into Table Design
And modify my requirement to simple way
thanks
rameshs_2000
March 10, 2008 at 8:38 am
You are welcome and please feel free to post more questions if you have them.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply