SQL Server: XACT_ABORT Option

DBA/Developer authored the UpdInventory stored procedure as follows:

CREATE PROCEDURE UpdInventory(@InvID int, @qty int)

AS BEGIN TRANSACTION
UPDATE InventorySET UnitsInStock = UnitsInStock - @qty
WHERE InvID = @InvID;
INSERT INTO InvQtyHistory(InvID, Adj, Description)
VALUES (@InvID, @qty, 'Inventory adjustment for purchase order');
COMMIT TRANSACTION

 

To ensure that if the UpdInventory stored procedure attempts to update the UnitsInStock table with a negative value, then no inserts or updates to any of the tables will be committed DBA/Developer sets the XACT_ABORT option to ON at the beginning of the AddPurchaseOrder stored procedure.

The XACT_ABORT option controls how SQL Server handles transactions when a run-time error occurs. When the XACT_ABORT option is set to ON, and a Transact-SQL statement raises an error at run time, the entire transaction is terminated and rolled back. When the XACT_ABORT option is set to OFF, only the Transact-SQL statement that raised the error is rolled back. The remaining statements in the transaction will be executed. The default value of the XACT_ABORT option is OFF. When transactions are nested, the outer transaction controls whether or not both transactions are committed or rolled back. Therefore, to ensure that all modifications take place or are rolled back, DBA/Developer can set the XACT_ABORT option to ON before beginning the outer transaction, namely the transaction started in AddPurchaseOrder. If an error occurs in AddPurchaseOrder, including an invalid update in UpdInventory, all statements in both transactions will be rolled back. DBA/Developer should not set the XACT_ABORT option to OFF at the beginning of the AddPurchaseOrder stored procedure. With this setting, each statement within the AddPurchaseOrder stored procedure would be considered individually. Some of the modifications might be committed, while others might not. DBA/Developer should not set the XACT_ABORT option to ON at the beginning of the UpdInventory stored procedure. This would control how modifications are committed within the UpdInventory stored procedure, but not in the outer procedure. DBA/Developer should not include the code for both stored procedures in a single stored procedure that includes a TRY...CATCH block. Although DBA/Developer can use a TRY...CATCH block to perform error processing, the UpdInventory stored procedure might be called from other Transact-SQL code. Therefore, this would not be the best choice, because it could affect other code.