T-SQL 事务2
启用事务完成转账存储过程
1 use StudentManager 2 go 3 if exists(select * from sysobjects where name='usp_TransferAccounts') 4 drop procedure usp_TransferAccounts 5 go 6 create procedure usp_TransferAccounts 7 @inputAccount int,--转入账户 8 @outputAccount int,--转出账户 9 @transferMoney int --交易金额 10 as 11 declare @errorSum int 12 set @errorSum=0 13 begin transaction 14 begin 15 update CardAccount set CurrentMoney=CurrentMoney-@transferMoney 16 where StudentId=@outputAccount 17 set @errorSum=@errorSum+@@error 18 update CardAccount set CurrentMoney=CurrentMoney+@transferMoney 19 where StudentId=@inputAccount 20 set @errorSum=@errorSum+@@error 21 if(@errorSum>0) 22 rollback transaction 23 else 24 commit transaction 25 end 26 go 27 --测试失败的转账 28 exec usp_TransferAccounts 100002,100001,1000 29 --查询余额 30 select Students.StudentId,StudentName,CurrentMoney from Students 31 inner join CardAccount on Students.StudentId=CardAccount.StudentId 32 --测试成功的转账 33 exec usp_TransferAccounts 100002,100001,800 34 --查询余额 35 select Students.StudentId,StudentName,CurrentMoney from Students 36 inner join CardAccount on Students.StudentId=CardAccount.StudentId
