one to one releation

[nasirmajor]

dear all,
a simple quetion for database experts.
can three datatables be updated at the same time (from one page)with
one table having one primary key
and other two tables are having that primary key as foreign key in
there tables.

tables with foreign keys are linked to parent table(having primary key)
with one-to-one releation.

this one-to-one releation was made due to large number of columns for
the same table, so it was divided.

what method will be sutible to update the record from same page ?

thanks in advance.

 

[Andrew Robinson]

A stored procedure that executes the update of both tables within a
transaction.

 

[nasirmajor]

A stored procedure that executes the update of both tables within a
transaction.

thankyou dear for reply
will you please give any link to the relavent article(one to one
updations).

 

[Jesse Liberty]

If two (or more) tables have a onene relation then they are really just
one big table. You could just combine them. that said, a transaction is
pretty easy. You could create a stored procedure that says something like

PROCEDURE [dbo].[mySproc] -- declare your stored procedure

@ModifiedBy varchar(100), -- pass in some parameters

@LongDescription varchar(2000),

@val1 int,

@Notes varchar(3000)

@val2 int

AS

BEGIN

Begin Transaction -- start the transaction

declare @foo as int -- local variables

declare @theTime as datetime

set @theTime = GetDate()

Insert into Table1 (col1, col2)

values (@theTime, @val1)

select @theIdentity= @@identity -- get the identity

if @@error <> 0 goto errorHandler -- if it fails, roll back

insert into table2( theIdentity, val1, theTime , ModifiedBy,
LongDescription, val2, Notes)

values (foo, 1, @theTime, @ModifiedBy, @LongDescription, @val2, @Notes)

if @@error <> 0 goto errorHandler -- if the second insert fails, roll it
all back

commit transaction -- otherwise, both scuceeded, so commit the transaction

goto done

errorHandler:
rollback transaction

done:
-- okay, so exit



Hope that helps.