事务的代码是这样的:
CREATE PROCEDURE udt_MoveMoney
(@FromID int,@ToID int,@Money int)
AS
BEGIN TRANSACTION --开启事务
Update StudentMoney set Money = Money - @Money WHERE ID = @FROMID --从转出帐户中转出指定费用
IF @@error <> 0 --如果发生错误，回滚
BEGIN
ROLLBACK TRANSACTION
RETURN
END
Update StudentMoney set Money = Money + @Money WHERE ID = @ToID --转入帐户中增加指定费用
IF @@error <> 0 --如果发生错误，回滚
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION --如果没有错误，提交
GO
数据库中只有两行记录集,
ID MONEY
1 4000
2 3000
调用此过程的代码是:
execute udt_MoveMoney 1,3,500
执行后变为:
ID MONEY
1 3500
2 3000
不明白是哪的问题,请高手指教!
------解决方案--------------------
执行了
因为ID存在
- SQL code
Update StudentMoney set Money = Money - @Money WHERE ID = @FROMID --从转出帐户中转出指定费用
------解决方案--------------------
- SQL code
create table StudentMoney(ID int, MONEY int)insert into StudentMoneyselect 1, 4000 union allselect 2, 3000CREATE PROCEDURE udt_MoveMoney(@FromID int,@ToID int,@Money int)ASBEGIN TRANSACTION --开启事务Update StudentMoney set Money = Money - @Money WHERE ID = @FROMID --从转出帐户中转出指定费用IF @@error <> 0 or @@ROWCOUNT=0 --如果发生错误，回滚BEGINROLLBACK TRANSACTIONRETURNENDUpdate StudentMoney set Money = Money + @Money WHERE ID = @ToID --转入帐户中增加指定费用IF @@error <> 0 or @@ROWCOUNT=0 --如果发生错误，回滚BEGINROLLBACK TRANSACTIONRETURN ENDCOMMIT TRANSACTION --如果没有错误，提交GOexecute udt_MoveMoney 1,3,500select * from StudentMoney/*ID MONEY----------- -----------1 40002 3000(2 row(s) affected)*/