Как заставить SET XACT_ABORT ON откатить транзакцию?

Question

На основе электронной документации по книгам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 на

http://msdn.microsoft.com/en-us/library/aa937483(v=sql.80).aspx

Answer 2

Когда вы используете xact abort on, в trycatch Заявление, вы можете вручную вызвать ошибку, чтобы сделать откат транзакции.

set xact_abort on;

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

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

end try
begin catch

end catch