1. try/catch

SET XACT_ABORT off

BEGIN TRY
BEGIN TRAN
--do sth here
COMMIT TRAN
PRINT 'Transaction committed'
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'Transaction rolled back'
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_LINE() as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure
END CATCH

2. 事务与异常处理

(1) 事务的3种状态:
a. active/committable
b. no open transaction
c. failed/doomed: 事务仍然处于激活状态,但是不能提交,不能执行任何写事务日志的操作. 这个状态只有在XACT_ABORT=on时才会出现.

可以使用XACT_STATE判断
0: no open transaction
1: active/committable
-1: failed/doomed

3. SavePoints

(1) 用 @@trancount 判断是否已经有活动的事务

(2) 创建一个save point
SAVE TRAN S1;

(3) 回滚到指定的save point
ROLLBACK tRAN s1;

[@more@]

ITPub博客

T-SQL 异常处理

最新文章