如何使SET XACT_ABORT ON回滚事务？(How to make SET XACT_ABO

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

备注

当SET XACT_ABORT为ON时，如果Transact-SQL语句产生运行时错误，整个交易被终止并回滚。

在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终止批，如果有一个错误 ：

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

这听起来很方便。 我怎样才能使它做到这一点？

Answer 1:

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

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错误信息水平

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

Answer 2:

当您使用XACT中止上，在try catch语句，你可以手动引发错误，使事务回滚。

set xact_abort on;

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

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

end try
begin catch

end catch

文章来源: How to make SET XACT_ABORT ON rollback the transaction?