解决办法一:
在begin transaction 前面加set xact_abort on
这样就可以在过程出错时中断执行，当然也就不能commit了
如此再在最后一个操作的后面加入 if @error=0 就说明所有的操作都执行成功了
就可以 commit
具体如:
set xact_abort on
begin trans
declare ...
<1>操作
<2>操作
...
<n>操作
if @error<>0
begin
rollback transaction
end
else
begin
commit transaction
end
end
解决办法二:
不加 set xact_abort on 而是声明一个记录错误的变量
比如：
begin trans
declare @nror int
set @nror=0
<1>操作
set @nror=@nror+@@error
<2>操作
set @nror=@nror+@@error
...
<n>操作
set @nror=@nror+@@error
if @ror<>0
begin
rollback transaction
end
else
begin
commit transaction
end
end
显然这样在每一个操作的背后都要加入一个记录错误的操作
最后再判断错误是不是0，如果不是则说明过程中某一步出了错，就不commit了。
这比第一种方法在烦一点，
CREATE PROCEDURE order_down_forjoin2
@result varchar(50) output,
@orderno varchar(50),
@out_trade_no varchar(50),
@product_class varchar(50),
@brand varchar(50),
@product_no varchar(30),
@product_size varchar(50),
@color varchar(100),
@color2 varchar(100),
@number int,
@price money,
@area varchar(50),
@city varchar(50),
@county varchar(50),
@address varchar(200),
@zip varchar(20),
@username varchar(50),
@phone varchar(50),
@mobile varchar(50),
@email varchar(100),
@joinno varchar(50),
@isems int,
@ems money,
@send varchar(400),
@memo text,
@sendinfo1 varchar(50),
@sendinfo1time datetime,
@sendinfo varchar(50),
/*库存的更新*/
@number55 int,
@number2 int,
@number3 int,
@number4 int,
@number5 int,
/*库存记录的插入*/
@number222 int,
@stock varchar(50),
@operation_type varchar(50),
@operation varchar(50),
@memo_product_inout_record text,
@users varchar(50),
/*备用金的更新*/
@checks varchar(16),
@lastprice money,
@memo_protect_log text,
@server varchar(50),
@disprice_class varchar(50),
@disprice money,
@price2 money
AS
begin TRANSACTION
declare @nror int
set @nror=0
insert into tbl_order
(orderno,out_trade_no,product_class,brand,product_no,product_size,color,color2,price,area,city,county,server,address,zip,username,phone,email,memo,joinno,isems,ems,send,sendinfo1,sendinfo1time,sendinfo,mobile,disprice,disprice_class)
values
(@orderno,@out_trade_no,@product_class,@brand,@product_no,@product_size,@color,@color2,@price,@area,@city,@county,@server,
@address,@zip,@username,@phone,@email,@memo,@joinno,@isems,@ems,@send,@sendinfo1,@sendinfo1time,@sendinfo,@mobile,@disprice,@disprice_class)
set @nror=@nror+@@error
update tbl_product_store set number=number-@number55,number2=number2-@number2,number3=number3-@number3,number4=number4-@number4,number5=number5-@number5
where product_no=@product_no and color=@color and product_size=@product_size
set @nror=@nror+@@error
insert into tbl_product_inout_record
(product_no,color,product_size,number2,stock,users,operation_type,operation,memo)
values (@product_no,@color,@product_size,0-
@number,@stock,@users,@operation_type,@operation,@memo_product_inout_record)
set @nror=@nror+@@error
update tbl_protect set price=@lastprice ,checks=@checks where joinno=@joinno
set @nror=@nror+@@error
insert into tbl_protect_log (joinno,orderno,price,product_no,memo) values (@joinno,@orderno,0-@price2,@product_no+'-'+@color+'-'+@product_size,@memo_protect_log)
set @nror=@nror+@@error
if @nror<>0
begin
set @result='失败'
rollback transaction
end
else
begin
set @result='成功'
commit transaction
end
GO
