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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/100682/viewspace-1022001/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/100682/viewspace-1022001/