游标+事务处理问题

[daijingjie2002]

---------------------------出入库过帐------------------------
CREATE PROCEDURE post_all
@inlib_post char(20),
@inlib_no char(20),
@name1 char(10)
AS
declare @in_lib_no varchar(20),
@item_no varchar(20),
@name varchar(50),
@qty float,
@unit varchar(10),
@unit_cost t_cost,
@total_cost t_cost,
@lib varchar(10),
@year int,
@month int,
@bumen varchar(10),
@return varchar(50)
if @inlib_post='INLIB'
BEGIN
select @lib=lib,@year=year,@month=month,@bumen=bumen from in_lib where in_lib_no=@inlib_no
declare inlib_cursor cursor for select in_lib_no,item_no,name,qty,unit,unit_cost,total_cost from in_lib_dtl where in_lib_no=@inlib_no
open inlib_cursor
fetch next from inlib_cursor into @in_lib_no,@item_no,@name,@qty,@unit,@unit_cost,@total_cost
begin tran
while @@fetch_status=0
begin
insert into item_jrn(inlib_no,lib,item_no,name,qty,bumen,unit_cost,unit,total_cost,inout,date,
func,year,month) values(@in_lib_no,@lib,@item_no,@name,@qty,@bumen,@unit_cost,@unit,@total_cost,'IN',getdate(),'ruku',@year,@month)

if (select count(*) from item_bal where item_no=@item_no and lib=@lib)>0
begin
update item_bal set qty=qty+@qty,total_cost=total_cost+@total_cost,unit_cost=(total_cost+@total_cost)/qty+@qty where item_no=@item_no and lib=@lib
end
else
begin
insert into item_bal(item_no,name,lib,qty,unit,total_cost,unit_cost) values(@item_no,@name,@lib,@qty,@unit,@total_cost,@unit_cost)
end

if (select count(*) from item_lgr where item_no=@item_no and lib=@lib and year=@year and month=@month)>0
begin
update item_lgr set qty_in=qty_in+@qty,cost_in=cost_in+@total_cost,qty_end=qty_beg+qty_in+@qty-qty_out,cost_end=cost_beg+cost_in+@total_cost-cost_out where
item_no=@item_no and lib=@lib and year=@year and month=@month
end
else
begin
insert into item_lgr(item_no,lib,year,month,name,qty_beg,qty_in,qty_out,qty_end,cost_beg,cost_in,cost_out,cost_end,unit_cost) values(@item_no,
@lib,@year,@month,@name,0,@qty,0,@qty,0,@total_cost,0,@total_cost,@total_cost/@qty)
end
fetch next from inlib_cursor into @in_lib_no,@item_no,@name,@qty,@unit,@unit_cost,@total_cost
end

update in_lib set post='Y',post_by=@name1,post_date=getdate() where in_lib_no=@inlib_no

if (@@error=0)
begin
commit tran
select @return='过帐完毕'
select @return
end
else
begin
rollback tran
select @return='过帐错误请检查数据一直性或是否有重复单据'
select @return
end
close inlib_cursor
deallocate inlib_cursor
END
ELSE IF @inlib_post='OUTLIB'
BEGIN
select @lib=lib,@year=year,@month=month,@bumen=out_people from out_lib where out_lib_no=@inlib_no
declare inlib_cursor cursor for select out_lib_no,item_no,name,qty,unit,unit_cost,total_cost from out_lib_dtl where out_lib_no=@inlib_no
open inlib_cursor
fetch next from inlib_cursor into @in_lib_no,@item_no,@name,@qty,@unit,@unit_cost,@total_cost
begin tran
while @@fetch_status=0
begin
insert into item_jrn(inlib_no,lib,item_no,name,qty,bumen,unit_cost,unit,total_cost,inout,date,
func,year,month) values(@in_lib_no,@lib,@item_no,@name,@qty,@bumen,@unit_cost,@unit,@total_cost,'OUT',getdate(),'chuku',@year,@month)

if (select count(*) from item_bal where item_no=@item_no and lib=@lib)>0
begin
if (select qty from item_bal where item_no=@item_no and lib=@lib)>=@qty
begin
update item_bal set qty=qty-@qty,total_cost=(qty-@qty)*unit_cost where item_no=@item_no and lib=@lib
end
else
begin
select '仓量不足'
goto error1
end
end
else
begin
goto error1
end

if (select count(*) from item_lgr where item_no=@item_no and lib=@lib and year=@year and month=@month)>0
begin
update item_lgr set qty_out=qty_out+@qty,cost_out=(qty_out+@qty)*unit_cost,qty_end=qty_beg+qty_in-@qty-qty_out,cost_end=cost_beg+cost_in-(qty_out+@qty)*unit_cost where

item_no=@item_no and lib=@lib and year=@year and month=@month
end
else
begin
goto error1
end
fetch next from inlib_cursor into @in_lib_no,@item_no,@name,@qty,@unit,@unit_cost,@total_cost
end

update out_lib set post='Y',post_by=@name1,post_date=getdate() where out_lib_no=@inlib_no
if (@@error=0)
begin
commit tran
select @return='过帐完毕'
select @return
end
else
begin
error1:
rollback tran
select @return='过帐错误请检查数据一直性或是否有重复单据'
select @return
end
close inlib_cursor
deallocate inlib_cursor
END
以上程序有时运行正常有时只能更新部分表，难道出错后不能回滚吗？请各位高手帮忙

[DJMPH]

SET XACT_ABORT ON
begin tran
.....
.....
commit
在出错后回滚所有修改。

[CrazyFor]

检查一下，是不是少判断了一些可能会出错的语句，也就是这样，因为没有回滚命令，所以只更新了部分数据。

[DJMPH]

还有，你存储过程本来就使用了事务，那么就要给嵌套事务起个名字。

[daijingjie2002]

难道每个表更新都要有一个事务？

[愉快的登山者]

@@error只对最后一个数据库操作是否正确做标志；
若在前面有INSERT或UPDATE不成功时，没有判断，
只要
update out_lib set post='Y',post_by=@name1,post_date=getdate() where out_lib_no=@inlib_no
就认为成功了。

[xiaoran27]

使用一个事务，在每个SQL后记下@@ERROR的值，在最后作一个COMMIT或ROLLBACK.
我认为这样比较好，且结构易读．

[愉快的登山者]

每一步都要进行成功否的判断。

[DJMPH]

个人认为不是所有的表都要一个事务，当你SET XACT_ABORT ON以后，无论你语句做错了什么sql都自动回滚，比如你里面的insert，如果主键重复就算出错。所以你需要一个总事务，然后在里面做你需要的东西，该加事务的加事务，不该加的不加。总事务最后只需要commit因为除了对就是错，而对于总事务错就自动回滚。
你可以试试，但是逻辑复杂了不保证不出问题。

[leimin]

try use 分布提交事务（嵌套事务）来处理！对每一的事务进行判断，最后提交全部的事务，
SET XACT_ABORT ON

[daijingjie2002]

leimin 能否说的清楚一点