在存储过程中使用事务时,如果没有try…catch语句,那么当set xact_abort on时,如果有错误发生,在批处理语句结束后,系统会自动回滚所有的sql操作。
当set xact_abort off时,如果有错误发生,在批处理语句结束后,系统会执行所有没有发生错误的语句,发生错误的语句将不会被执行。
在存储过程中使用事务时,如果存在try…catch语句块,那么当捕获到错误时,需要在catch语句块中手动进行Rollback操作,否则系统会给客户端传递一条错误信息。
如果在存储过程开始处将set xact_abort on,那么当有错误发生时,系统会将当前事务置为不可提交状态,即会将xact_state()置为-1,此时只可以对事务进行Rollback操作,不可进行提交(commit)操作,那么我们在catch语句块中就可以根据xact_state()的值来判断是否有事务处于不可提交状态,如果有则可以进行rollback操作了。
如果在存储过程开始处将set xact_abort off,那么当有错误发生时,系统不会讲xact_state()置为-1,那么我们在catch块中就不可以根据该函数值来判断是否需要进行rollback了,但是我们可以根据@@Trancount全局变量来判断,如果在catch块中判断出@@Trancount数值大于0,代表还有未提交的事务,既然进入catch语句块了,那么还存在未提交的事务,该事务应该是需要rollback的,但是这种方法在某些情况下可能判断的不准确。
推荐的方法还是将set xact_abort on,然后在catch中判断xact_state()的值来判断是否需要Rollback操作。
create table #temp(aa varchar(20))
set xact_abort on
BEGIN TRY
-- Generate divide-by-zero error.
begin tran
insert into #temp select '00000'
SELECT 1/0;
commit tran
END TRY
BEGIN CATCH
if XACT_STATE() = -1
begin
rollback tran
PRINT
'ErrorNumber :'+CAST( ERROR_NUMBER() AS VARCHAR(20)) + char(13)+char(10)+
'ErrorMessage:'+ERROR_MESSAGE()+ char(13)+char(10)+
'ErrorLine:'+CAST(ERROR_LINE()AS VARCHAR(20))
--ERROR_NUMBER() AS ErrorNumber,
--ERROR_SEVERITY() AS ErrorSeverity,
--ERROR_STATE() AS ErrorState,
--ERROR_PROCEDURE() AS ErrorProcedure,
--ERROR_LINE() AS ErrorLine,
--ERROR_MESSAGE() AS ErrorMessage;
end
END CATCH;
select * from #temp
drop table #temp
/*
(1 行受影响)
-----------
(0 行受影响)
ErrorNumber :8134
ErrorMessage:遇到以零作除数错误。
ErrorLine:9
aa
--------------------
(0 行受影响)
*/