SQL Triggers- HelpNeeded - NewPerson - Stored Procedures, Triggers, & Functions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » SQL Triggers- HelpNeeded - NewPerson

SQL Triggers- HelpNeeded - NewPerson

Options

Previous Topic · Next Topic

rameshs_2000

Posted: Monday, March 10, 2008 7:30:39 AM

Rank: Newbie

Joined: 3/10/2008
Posts: 1
Points: 3

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

Scott Whigham

Posted: Thursday, March 13, 2008 5:11:41 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

tl;dr

That's the shortest amount you could use for your example? Really? The ability to shorten a problem proves that you have an understanding of what you want. Whenever I see long code samples like this on forums, my first thought is that the user doesn't really understand the problem and, to compensate, they just dump out code. If you can summarize/simplify this down I'll be happy to help but I'm not wading through 76 LINES OF CODE to surmise what you are asking. Perhaps I'm wrong and this is the shortest example you could provide - it's possible - and, if that's the case, just say so Smile

Make it shorter and I can help. Make it *easy* for me to help you and I'll help.

(I know - you're probably thinking, "People always ask for DDL/schema/better examples in forums so I'll go ahead and post everything." The problem is that you've posted lots of what-I-guess-to-be unnecessary info. Was it necessary that you include *all* of those columns, for example, or could you have just posted 5 or 6 columns? Was it necessary to post the ALTER TABLE syntax for the FK or could you have written one comment that said "-- FK on this to that"?)

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.