『SQLServer系列教程』——事务详解

📣读完这篇文章里你能收获到

• 了解SQLServer事务的特性及分类
• 事务的创建过程
• 提供实际操作的案例SQL脚本

一、概念篇

1.什么是事务

2.事务的特性

3.事务的分类

二、事务的创建

三、案例代码

示例1：使用事务回滚有错误的数据

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

示例2：启用事务的存储过程

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,1000
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId
--测试成功的转账
exec usp_TransferAccounts 100002,100001,800
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId