嵌套事务回滚示例

参考： sql事务（Transaction）用法介绍及回滚实例

-- 测试表
create table T1
	(
	ID int identity(1,1),
	Code nvarchar(10) 
	)
Go

-- 存储过程
create procedure p_RollbackTest 
as 
begin
	set nocount on
	declare @n int = 0 
	
	begin tran outTrans
	insert into T1(Code) values('A')		-- 保存入表中	

	begin try 
		begin tran innerTrans 

		insert into T1(Code) values('B')	-- savepoint之前，保存入表中

		save tran savepoint			-- 创建事务保存点 
		
		insert into T1(Code) values('C')	-- savepoint之后，丢弃
				
		set @n = 10 / 0				-- 异常
		
		commit tran innerTrans 
	end try 
	begin catch 
		rollback tran savepoint			--回滚到保存点 
		commit tran innerTrans			-- 注意：这里用rollback tran会出错，只能用commit tran
	end catch 
	
	insert into T1(Code) values('D')		-- 保存入表中		
	commit tran outTrans	
end
Go 

Exec p_RollbackTest
Go 

select * from T1 
Go