Exam 70-762 topic 1 question 140 discussion

Actual exam question from Microsoft's 70-762

Question #: 140
Topic #: 1

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 OrderDetail 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 code:

Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. Can add a variable in the BEGIN TRY block, and test the variable in the BEGIN CATCH block.
Note: XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
XACT_STATE =1: the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql

by marco13 at June 11, 2020, 9:51 p.m.

Comments

Submit Cancel

giuPigna

4 years, 5 months ago

Let's start to say XACT_ABORT is OFF, so if an update fails this does not mean the other updates cannot be performed (and committed). If the first 2 updates (Customers) are success, we need to commit the trans. If all 4 updates are success we need to commit the trans as well. This means that if the second update (Customer) fails OR (the first 3 are ok, but the 4th one fails) we need to rollback the trans. So I would use a variable after 2nd update customer. In the Catch statement, we need to check if the state is 1 (there is something to commit), then check the variale: 1. If the first 2 updates was success and fails after, I rollback the trans to savepoint and commit the first 2 updates) 2. Otherwise i rollback the trans

upvoted 2 times

...

SoupDJ

4 years, 6 months ago

If END TRAN is not a typo, then the entire statement is syntactically incorrect. And B is the answer; However, assume they meant END TRY - failure in the UPDATE to either of the ORDER tables could force XACT_STATE = -1 (theoretically) which would mean that the UPDATE to customers never gets committed - again the answer is B.

upvoted 1 times

...

CristianCruz

4 years, 8 months ago

and rollback? anwers is B

upvoted 1 times

...

marco13

4 years, 11 months ago

I think the real reason for answering No is down to the "END TRAN", which is meaningless

upvoted 1 times

lh2607

4 years, 5 months ago

I presume that's a typo from copying it over from the original source. I don't think the exam tries to trick you out like that, they're more interested in your knowledge of transactions rather than something as simple as ending the try.

upvoted 1 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 140 discussion

Comments

giuPigna

SoupDJ

CristianCruz

marco13

lh2607

SY0-701