If two (or more) tables have a one:one 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.
--
Jesse Liberty
Author, Programmer
Microsoft MVP
<na********@yahoo.comwrote in message
news:11**********************@s16g2000cws.googlegr oups.com...
>
Andrew Robinson wrote:
>A stored procedure that executes the update of both tables within a
transaction.
<na********@yahoo.comwrote in message
news:11********************@j8g2000cwa.googlegrou ps.com...
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.
thankyou dear for reply
will you please give any link to the relavent article(one to one
updations).
