以下是一个注册的存储过程，插入4个表。经过测试，如果其中的某个表插入失败，事务并
不能回滚，请问当其中某个表插入失败的时候如何才能回滚事务，把已经插入成功的表回滚。（即要求要么全部插入成功，要么全部不插入，不能有的表插入成功，有的表插入失败）
CREATE PROCEDURE [dbo].[Reg]

@MenberID varchar(20),
@PassWord varchar(20),
@CompanyName varchar(100),
@Dept varchar(50),
@Add1 varchar(100),
@Add2 varchar(100),
@City varchar(50),
@Province varchar(50),
@CountryOrRegin varchar(50),
@ContactPerson varchar(20),
@PostCode varchar(20),
@Tel varchar(50),
@Fax varchar(50),
@Email varchar(50),
@WebSite varchar(100),
@InstantMessage varchar(100),
@HirePeopleID varchar(10),
@TurnOverID varchar(10),
@BizTypeID varchar(10),
@MainProduct varchar(500),
@BriefIntr text,
@BizRangeStr varchar(150)
AS

begin tran mytran
/*插入表1*/
Insert EN_MENBER(MenberID,PassWords ,CompanyName ,Dept ,Add1,
Add2,City,Province ,CountryOrRegin ,ContactPerson ,PostCode,Tel ,Fax,Email ,
WebSite ,InstantMessage ,HirePeopleID,TurnOverID ,BizTypeID ,
MainProduct ,BriefIntr )
values
(@MenberID ,
@PassWord ,
@CompanyName,
@Dept ,
@Add1,
@Add2,
@City ,
@Province ,
@CountryOrRegin ,
@ContactPerson ,
@PostCode ,
@Tel ,
@Fax,
@Email,
@WebSite ,
@InstantMessage,
@HirePeopleID ,
@TurnOverID ,
@BizTypeID ,
@MainProduct,
@BriefIntr
)

/*插入表2*/

EXEC(@BizRangeStr)

/*插入表3*/

Insert EN_MENBER_AprRcd(MenberID,AprStateID,Operator,OperatorPos)
values(@MenberID,'1','C','H')

/*插入表4*/

Insert EN_MENBER_AmndRcd(MenberID,AmendStateID,Operator,OperatorPos)
values(@MenberID,'1','C','H')

Commit tran mytran
GO

看看这个吧

USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @del_error = @@ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @ins_error = @@ERROR

-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO

[此贴子已经被作者于2006-2-13 16:35:50编辑过]

谢谢，我知道思路了