如何使 SET XACT_ABORT ON 回滚事务? [英] How to make SET XACT_ABORT ON rollback the transaction?
问题描述
基于 SET XACT_ABORT ON 的在线文档
代码>,我的印象是,如果 T-SQL 语句引发运行时错误,则整个事务将终止并回滚:
Based on the Books Online documentation of SET XACT_ABORT ON
, i get the impression that if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back:
当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL 语句引发运行时错误,则整个事务将终止并回滚.
Remarks
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
在 SQL Server 2008 R2 中对此进行测试:
Testing this in SQL Server 2008 R2:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50))
DROP TABLE QuertyAsdf
PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))
给出输出:
TranCount befor an error = 1
Msg 3701, Level 11, State 5, Line 6
Cannot drop the table 'QwertyAsdf', because it does not exist or you do not have permission.
TranCount after an error = 1
我还认为 SET XACT_ABORT ON
会在出现错误时终止批处理:
i was also under the impression that SET XACT_ABORT ON
terminates the batch if there's an error:
SET XACT_ABORT ON 指示 SQL Server 回滚整个事务并在发生运行时错误时中止批处理.
SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.
听起来很方便.我怎样才能让它也这样做?
That sounds handy. How can i make it do that too?
推荐答案
SQL Server 仅在 Severity level 大于或等于 16 时回滚事务.
The SQL Server only rollback transactions when Severity level greater or equals 16.
参见示例:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' whenIDENTITY_INSERT is set to OFF.
在 SQL Server 2008 R2 上测试
Test on SQL Server 2008 R2
SET XACT_ABORT ON;
BEGIN TRANSACTION;
PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50))
insert into ORC_ORCAMENTO (ORCID, ORCNOME, ORCATIVO) VALUES (1, 'TESTE_ALEXP', 0);
PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))
退货
TranCount befor an error = 1
Msg 544, Level 16, State 1, Line 5
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' when IDENTITY_INSERT is set to OFF.
TranCount after an error = 0
查看 Microsoft 错误消息级别
See Microsoft Error Message Levels on
这篇关于如何使 SET XACT_ABORT ON 回滚事务?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!