SET XACT_ABORT ON (SQL Server)

SET XACT_ABORT ON (SQL Server) is a SQL statement in the Transactions category. SQL Server: automatically rolls back the entire transaction if any statement raises a runtime error. The syntax is SET XACT_ABORT ON;. It returns auto-rollback behavior. A typical example: SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO log VALUES ('Step 1'); SELECT 1/0; -- Divide by zero error INSERT INTO log VALUES ('Step 2'); -- Not executed COMMIT TRANSACTION; -- Not reached, transaction rolled back -- Without SET XACT_ABORT ON: -- The INSERT might commit while the SELECT fails -- Partial transactions = data inconsistency -- WITH XACT_ABORT ON: -- ALL statements roll back, consistent state -- Also works with:… A close relative is BEGIN / START TRANSACTION, which begins a new transaction. Changes made within are invisible to others until COMMIT. A close relative is COMMIT, which saves all changes made in the current transaction permanently.