Insert a record on a one-to-many table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert a record on a one-to-many table

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-08 : 12:18:35

I have a table called OLTSUPDATES. I want a trigger that would update two tables (DMAIN & CONTACTS). This is a ONE(dmain) to MANY(contacts) relation based on the DONOR_ID field.

Currently a trigger in DBO.OLTSUPDATES updates my DMAIN table with no problem.
----------------
USE YourDatabaseGoesHere
Go
CREATE trigger trigger_DmainUpdate On dbo.OltsUpdates
FOR INSERT
AS
BEGIN
Insert DMAIN (CaseNo, DFName, DLName,DDate,DoB,Hosp_id,Sex,Race,DCause,ReferralType)
Select CaseNo,
DFName,
DLName,
DDate,
DoB,
Hosp_id,
Sex,
Race,
DCause,
ReferralType
From Inserted
END
----------------
However, in DMAIN there is an autonumber (DONOR_ID) that is created upon new insert and because of the one-to-many relation with CONTACTS table, now I want a trigger to insert a new record in DBO.CONTACTS, that will update data from DBO.OLTSUPDATES but also include the newly created autonumber.

I was thinking. Should I create another trigger to update my OLTSUPDATE table with the new autonumber, then have the trigger below update the CONTACTS table. Is there a simpler way for this?

CREATE trigger trigger_OLTSInsertToContact On bt1.dbo.OltsUpdates
FOR INSERT
AS
BEGIN
Insert CONTACTS (CaseNo, ConFName, ConLName, ConStreet, ConStreet2, ConCity, ConState, ConZip, ConHomePH, Relationship)
Select CaseNo,
ConFName,
ConLName,
ConStreet,
ConStreet2,
ConCity,
ConState,
ConZip,
ConHomePH,
Relationship

From Inserted
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 12:28:02

[code]Cant you do both inserts inside same trigger?
USE YourDatabaseGoesHere
Go
CREATE trigger trigger_DmainUpdate On dbo.OltsUpdates
FOR INSERT
AS
BEGIN

DECLARE @DONOR_ID int

Insert DMAIN (CaseNo, DFName, DLName,DDate,DoB,Hosp_id,Sex,Race,DCause,ReferralType)
Select CaseNo,
DFName,
DLName,
DDate,
DoB,
Hosp_id,
Sex,
Race,
DCause,
ReferralType
From Inserted

SET @DONOR_ID=SCOPE_IDENTITY()

Insert CONTACTS (fields)
Select @DONOR_ID,other fields
From Inserted

END[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources