SQLServer -- 事务的应用

在存储过程的应用

--use StudentManager
--go

----事务基本框架
--declare @errorSum int --定义变量，用于累计事务执行过程中的错误
--set @errorSum =0 --初始化为0，即无错误
--begin transaction
--  begin
--      update CardAccount set CurrentMoney=CurrentMoney-1000
--      where StudentId=100001
--      set @errorSum=@errorSum+@@ERROR

--      update CardAccount set CurrentMoney=CurrentMoney+1000
--      where StudentId = 100002
--      set @errorSum = @errorSum+@@ERROR
--      if(@errorSum>0)
--          rollback transaction
--      else
--          commit transaction  --提交回滚事务
--  end

--go

--select Students.StudentId,StudentName,CurrentMoney from Students
--inner join CardAccount on Students.StudentId=CardAccount.StudentId


--update CardAccount set CurrentMoney=CurrentMoney+900
--      where StudentId=100001
--select * from CardAccount


--事务的应用
use StudentManager
go

if exists(select * from sysobjects where name='usp_TransferAccounts')
drop procedure usp_TransferAccounts
go

create procedure usp_TransferAccounts
@inputAccount int,
@outputAccount int,
@transferMoney int
as
    declare @errorSum int 
    set @errorSum=0
    begin transaction
    begin
        Update CardAccount set CurrentMoney= CurrentMoney - @transferMoney 
        where StudentId=@outputAccount
        set @errorSum=@errorSum+@@ERROR
        update cardAccount set CurrentMoney=CurrentMoney + @transferMoney
        where StudentId =@inputAccount
        set @errorSum=@errorSum+@@ERROR
        if(@errorSum>0)
            rollback transaction
        else 
            commit transaction
    end

go
exec usp_TransferAccounts 100002,100001,100
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=cardAccount.StudentId


select * from CardAccount