Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
Runs within a single transaction.
Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:
Does the solution meet the goal?
A. Yes
B. No
I agree with B: there are two errors in the rollback part:
1. The transaction needs to rollback to the savepoint (does not happen in the exibit)
2. XACT_STATE() cannot be equal to 1 in a catch block, since 1 indicates no error has happened and transaction is committable.
Jay2, you are wrong.
1. The full rollback must be done when @CustomerComplete=0,
2. XACT_STATE() can be equal 1 if error happens. 1 means that that is an open transaction and there is something to be committed. XACT_STATE() is not about errors. It is about a possibility to Commit. Errors in one command does not prevent us from committing other commands.
“SAVE TRAN TR1” command is not necessary in this script and is misleading us, but it is not an error.
Correct answer is A.