ABORT ON rollback the transaction?

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:

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.

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))

Gives the output:

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

i was also under the impression that SET XACT_ABORT ON terminates the batch if there's an error:

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?

---

The SQL Server only rollback transactions when Severity level greater or equals 16.

See example:

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.

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))

Returns

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

See Microsoft Error Message Levels on

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

---

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

set xact_abort on;

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

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

end try
begin catch

end catch

链接地址: http://www.djcxy.com/p/61814.html

上一篇: PHP排序问题，arsort vs asort +数组

下一篇: ABORT ON回滚事务？