XACT_ABORT

SELECT CASE 

    WHEN ( ( ( @@OPTIONS & 512 ) = 512 ) ) THEN 'ON' 

    ELSE 'OFF' 

END AS XACT_ABORT_STATUS;

-- When XACT_ABORT is ON → any runtime error rolls back the entire transaction automatically.

-- When OFF → some errors (like PK violations) do not abort the transaction, and subsequent statements still run.

SET XACT_ABORT ON;

CREATE TABLE MyTable (

    Id INT PRIMARY KEY,

    Name VARCHAR(100)

);

SELECT * FROM MyTable;

BEGIN TRANSACTION;

INSERT INTO MyTable (Id, Name) VALUES (1, 'Alice');

INSERT INTO MyTable (Id, Name) VALUES (1, 'Bob');

-- INSERT INTO MyTable (Id, Name)

-- SELECT 1, 'Bob'

-- WHERE NOT EXISTS (

--     SELECT 1

--     FROM MyTable

--     WHERE Id = 1

-- );

SELECT * FROM MyTable;

commit;

SELECT * FROM MyTable;

Output is

Output:

303 ms

XACT_ABORT_STATUS

-----------------

ON               

Id          Name                                                                                                

----------- ----------------------------------------------------------------------------------------------------

Msg 2627, Level 14, State 1, Server 6f1d64a248a3, Line 23

Violation of PRIMARY KEY constraint 'PK__MyTable__3214EC07149F63D5'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1).

Eğer OFF yaparsak

Output:

433 ms

XACT_ABORT_STATUS

-----------------

ON               

Id          Name                                                                                                

----------- ----------------------------------------------------------------------------------------------------

Msg 2627, Level 14, State 1, Server de2db788b1d3, Line 23

Violation of PRIMARY KEY constraint 'PK__MyTable__3214EC078BA094A9'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (1).

The statement has been terminated.

Id          Name                                                                                                

----------- ----------------------------------------------------------------------------------------------------

          1 Alice                                                                                               

Id          Name                                                                                                

----------- ----------------------------------------------------------------------------------------------------

          1 Alice