USE OF XACT_ABORT

With use of XACT_ABORT we can manage either entire transaction will rollback or
only statement which raises error

When XACT_ABORT is ON entire transaction will rollback in case of run time error



When XACT_ABORT is OFF depends of severity either the transaction will rollback or commit
OFF is default setting

This setting is set at runtime or execute time.

Lets create a  table and insert data into it
We will generate arithmatic overflow error


CREATE TABLE temp
    (
         id   INT,
         data VARCHAR(100)
    )

First we will set XACT_ABORT OFF




SET XACT_ABORT OFF

TRUNCATE TABLE temp

BEGIN TRAN

INSERT INTO temp
VALUES      (1,'a')

INSERT INTO temp
VALUES      (123432232333,'a')

COMMIT TRAN



Here we can see first insert statement committed but second one only rollback.
Not entire transaction



SELECT *
FROM   temp




Now we will set XACT_ABORT ON





SET XACT_ABORT ON

TRUNCATE TABLE temp

BEGIN TRAN

INSERT INTO temp
VALUES      (1,'a')

INSERT INTO temp
VALUES      (123432232333,'a')

COMMIT TRAN


Here when error comes entire transcation will be rollback


SELECT *
FROM   temp






No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts