Insert into multiple tables

[kafka]

I have a form which submits to a SQL Server 2000 database.

I have a registration form for a single user which adds data to three separate tables. I need to insure that when the data commits and writes a new row to the first table and adds a new unique primary id, the second and third table grab and insert that primary key into their tables for that record of that same user to keep the association with that unique record of the first table.

I need to insure that when alot of users are adding and updating against the database that I maintain integrity and that data in different tables maintain their relationships.

It's a bit high level, but any help as well as any sample code would be greatly appreciated.

Best,

G

 

[essa2000]

Dear ;

First of all , make your primary key as Identity (1,1) of your first table and then create SP like that ;

--- it is algorithm

Create SP

@P_Var1 int
@P_Var2 int

As

Declare @L_PrimaryID int

Begin Trans

Insert into Table1 (Field1 ,Field2) Values ('Essa' , 'essa')

SET @L_PrimaryID = Cast(@@IDENTITY as int)

If CAST(@@Error as int) > 0 ErrHandler

Insert into Table2 (Field1 , field2) Values (@L_PrimaryID , 'essa')

If CAST(@@Error as int) > 0 ErrHandler


Insert into Table3 (Field1 , field2) Values (@L_PrimaryID , 'essa')

If CAST(@@Error as int) > 0 ErrHandler


Commit trans

Return 0

ErrHandler:

Rollback Trans



Now , after adopting this procedure , there is no possibility of duplicate values in multiple user enviornment.

Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com