Re: inserting into two tables and transaction problem
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Fri, 30 Jun 2006 12:03:23 -0700
Habib wrote:
CREATE PROCEDURE SimpleInsert (
@custname custname_type,
@carname carname_type)
AS
BEGIN TRANSACTION
INSERT Customers (CustName) VALUES (@custname)
IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
DECLARE @custid int
SELECT @custid = scope_identity()
INSERT Cars (ID, CarName) VALUES (@custid, @carname)
IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACTION
IF no error this works but to test transaction, I chanded the table
name of second insert to 'car' in which doesn't exist and this error
occured (calling sp by ASP .NET page):
Invalid object name 'car'. Transaction count after EXECUTE indicates
that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1.
Recompile the procedure. If you rename the table the procedure's
stored query plan is invalid. You have found a type of error for
which a procedure will not continue. If you simply want to test
the transaction, make a unique index on the Customers.CustName
column, and then try to run the procedure twice with the same
customer name.
Joe Weinstein at BEA Systems
Thanks
Habib
.
- References:
- Prev by Date: Re: Update statement, then insert what wasn't available to be updated.
- Previous by thread: inserting into two tables and transaction problem
- Next by thread: Does anyone know of a tool that allows a SQL Server database to be
- Index(es):
Relevant Pages
|
