SET XACT_ABORT allows SQL Server to automatically rolls back the complete transaction when a T-SQL statement raises a runtime error.
SET XACT_ABORT is used in SQL Server to control the behavior of a batch of Transact-SQL statements. When set to ON, it tells SQL Server to roll back the entire transaction if any statement in the batch raises an error, rather than just rolling back the individual statement that raised the error. This can be useful if you want to ensure that no partial changes are made to the database if an error occurs.
When to use SET XACT_ABORT: SET XACT_ABORT should be used when there is a risk of partial changes being made to the database if an error occurs. This could be the case in complex transactions with multiple statements that need to either succeed or fail as a group.
Why to use SET XACT_ABORT: SET XACT_ABORT is used to ensure that all statements in a transaction succeed or fail as a group. This helps to ensure that your database is consistent and not left in an inconsistent state if an error occurs.
How to use SET XACT_ABORT: SET XACT_ABORT is used to control the behavior of a batch of Transact-SQL statements.
To use SET XACT_ABORT, you must use it at the beginning of the batch, before any other statements:
SET XACT_ABORT ON
BEGIN TRANSACTION
-- Transact-SQL statements go here
COMMIT TRANSACTION
Example:
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: XACT_ABORT example SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test1', '1');
INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test2', '2');
INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test3', '3');
COMMIT TRANSACTION;
