如何使 SET XACT_ABORT ON 回滚事务？

Question

根据 SET XACT_ABORT ON 的联机丛书文档，我得到的印象是，如果 T-SQL 语句引发运行时错误，则整个事务将终止并回滚：

当 SET XACT_ABORT 处于打开状态时，如果 Transact-SQL 语句引发运行时错误，则整个事务将终止并回滚。

在 SQL 服务器 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

我还认为如果出现错误，＜code＞将XACT_ABORT设置为ON＜/code＞会终止批处理：

SET XACT_ABORT ON 指示 SQL Server 回滚整个事务，并在发生运行时错误时中止批处理。

听起来很方便。我怎么能让它也这么做？

Answer 1

启用xact abort时，在＜code＞try＜code＞和＜code＞catch＜code＞语句中，您可以手动引发错误以使事务回滚。

set xact_abort on;

begin try
    ...dml statements here....

if conditions here...
    raiseerror(....);

end try
begin catch

end catch

Answer 2

SQL Server 仅在严重性级别大于或等于 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.

在SQLServer 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

请参阅微软错误消息级别

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors