SQL Server/T-SQL Tutorial/System Settings/XACT ABORT

Материал из SQL эксперт
Перейти к: навигация, поиск

Modifying exception types with XACT_ABORT

   <source lang="sql">

4> SET XACT_ABORT ON 5> SELECT POWER(2, 32) 6> PRINT "This will NOT print!" 7> GO Msg 232, Level 16, State 3, Server J\SQLEXPRESS, Line 5 Arithmetic overflow error for type int, value = 4294967296.000000. 1> 2></source>


XACT_ABORT and transactions

   <source lang="sql">

4> SET XACT_ABORT ON 5> BEGIN TRANSACTION 6> GO 1> SELECT 1/0 AS DivideByZero 2> GO Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 1 Divide by zero error encountered. 1> SELECT @@TRANCOUNT AS ActiveTransactionCount 2> GO ActiveTransactionCount 

                    0

(1 rows affected) 1> 2> 3></source>


XACT_ABORT in a stored procedure

   <source lang="sql">

6> CREATE PROCEDURE NoRollback 7> AS 8> BEGIN 9> SET XACT_ABORT ON 10> 11> BEGIN TRANSACTION 12> INSERT SomeData VALUES (1) 13> 14> INSERT SomeData VALUES (1/0) 15> COMMIT TRANSACTION 16> END 17> GO 1></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/System_Settings/XACT_ABORT&oldid=6591»