June 9, 2008 at 5:32 am
i have two table
1.contractorInfo which fields are
contractorid(primary key) bigint...aoutoincrement
name varchar (50) and
2.contractorRegistration
regid(primary key) bigint aoutoincrement
contractorid(foreign key) bigint
Now i have a form contractor registration .on submitting i have to insert these table at the same time mean record will insert both table one at a time and the primary key value and the foreign key value should be same .How can i do this.
June 9, 2008 at 5:58 am
Create trigger for the same..:D
June 9, 2008 at 6:15 am
You may want to throw in some error handling, but it would look something like this:
[font="Courier New"]CREATE PROCEDURE appInsertContractorRegistration
@ContractorName VARCHAR(50)
, @ContractorID INT = 0 OUTPUT
, @RegID INT = 0 OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT ContractorInfo ([Name]) VALUES (@ContractorName)
SELECT @ContractorID = SCOPE_IDENTITY()
INSERT ContractorRegistration (ContractorID) VALUES (@ContractorID)
SELECT @RegID = SCOPE_IDENTITY()
COMMIT TRAN
RETURN(0)[/font]
June 9, 2008 at 6:51 am
The key to Michael's answer is "scope_identity()". Look at that one in Books Online. Very useful tool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 12:31 am
Thank you Michael Earl Its works now.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply