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
Explanation:
http://stackoverflow.com/questions/11444923/stored-procedure-to-update-multiple-tables
Answer is not clear from the Link. What should be the answer? I guess it’s ‘Yes’ right?
5
0
The answer is yes. The Update Customer and CustomerInfo will be commited if there is no error in those two updates.
20
2
You are totally correct! The answer is yes
8
2
The answer is NO
If error occurs on Update OrderHeader, the 2 previous updates will be rolled back.
Save tran doesnt commit transaction.
So : “Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.” is not verified
4
12
Save transaction is not changing anything here. In case of error on update order header or detail, the transaction will be committed in catch because @CustomerComplete variable will be set to one.
IMHO answer is true.
4
0
the answer is NO because that code does not guarantee the first requirement (only the second is guaranteed)
“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.”
IF the first two updates went ok, and there was been errors in the second two updates than the catch block is activated:
what happens in the catch block?
first check if @CustomerComplete is 1 AND xact_state is 1 : if in the procedure there is “SET XACT_ABORT_OFF” xact_state is always 1, so the right code to satify the first requirement is ROLLBACK TRANSACTION TR1 (the savepoint) and then COMMIT TRANSACTION.
if SET XACT_ABORT_ON is not possibile to rollback to the savepoint.
by the way that code does not satisfy the requirement so the answer is NO.
12
0
My comment is ok in my uderstanding of “commit only the first 2 update” if there is any error in the second 2 update. but i trusT i’m wrong. the question says “regardless” the state , so i must do not care and manage the seconds 2 updates. so the answer is YES
0
3
It will work at least if the code was correct lol .
should be end try and not end tran and for the roll back it s xact_state = -1
2
0
ok, there is a typo : it should be END TRY , not END TRAN..so the anwser is YES, IT is just a typo or the ansWer is NO, this code is not working because the “END TRAN” ?
3
2