You are a database developer on an instance of SQL Server 2008. Your Prod database contains the following tables:
You have a stored procedure, AddPurchaseOrder, which accepts a table-valued input parameter containing the purchase order details, begins a transaction, and inserts the appropriate rows into the PurchaseOrder and POLines tables.
The AddPurchaseOrder stored procedure then calls the UpdInventory stored procedure to update inventory quantities for the products sold on the purchase order. The UpdInventory stored procedure is defined as follows:
CREATE PROCEDURE UpdInventory(@InvID int, @qty int)AS
BEGIN TRANSACTION
UPDATE Inventory SET UnitsInStock = UnitsInStock - @qty WHERE InvID = @InvID;
INSERT INTO InvQtyHistory(InvID, Adj, Description)VALUES (@InvID, @qty, ‘Inventory adjustment for purchase order’);
COMMIT TRANSACTION
The Inventory table contains a CHECK constraint on the UnitsInStock column that prevents it from being updated to a negative value. You want 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.
Which action should you take?
A. Set the XACT_ABORT option to OFF at the beginning of the AddPurchaseOrder stored procedure.
B. Set the XACT_ABORT option to ON at the beginning of the AddPurchaseOrder stored procedure.
C. Set the XACT_ABORT option to ON at the beginning of the UpdInventory stored procedure.
D. Include the code for both stored procedures in a single stored procedure that includes a TRY…CATCH block.
Correct Answer: B
Explanation/Reference:
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 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.
In this scenario, you also have nested transactions. The AddPurchaseOrder stored procedure begins a transaction and then calls the UpdInventory stored procedure, which begins another transaction. 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, you 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.
You 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.
You 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.
You should not include the code for both stored procedures in a single stored procedure that includes a TRY…CATCH block. Although you 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.
