HOTSPOT - You have the following stored procedure: You run the following Transact-SQL statements: What is the result of each Transact-SQL statement? To answer, select the appropriate options in the answer area. Hot Area:
Suggested Answer:
Box 1: All transactions are rolled back. The first IF-statement, IF @CODE = 'C2323' AND @ApplicationID = 1, will be true, an error will be raised, the error will be caught in the CATCH block, and the only transaction that has been started will be rolled back. Box 2: Only Log1, Log2, and Log3 tables are updated. The second IF-statement, IF @Code = 'C2323', will be true, so the second transaction will be rolled back, but log1, log2, and log3 was updated before the second transaction.
For both case, correct answer:All Transactions are Rolled Back
Ref:"When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. "
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-2016
Agree - one rollback at any level of transaction stack rolls back everything. Both result in everything being rolled back. RAISERROR transfers control to CATCH block with XACT_STATE=1
Agree - one rollback at any level of transaction stack rolls back everything. Both result in everything being rolled back. RAISERROR transfers control to CATCH block with XACT_STATE=1
Question here, I changed the IF @Code = 'C2323' --ROLLBACK TRAN
to COMMIT TRAN
It actually changes my @@TRANCOUNT from 2 -> 0, I would have expected 1 and it to not actually commit b/c in a nested TRAN
correct answer for both : All Transactions are Rolled Back
upvoted 9 times
...
This section is not available anymore. Please use the main Exam Page.70-761 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Tazul
Highly Voted 5 years, 9 months agoLuzix
4 years, 6 months agoTazul
Highly Voted 5 years, 9 months agoM4x
5 years, 9 months agoAnirudh_net
Most Recent 3 years, 3 months agoVermonster
4 years, 5 months agoOooo
4 years, 9 months agoAnette
5 years, 1 month agodaniel_yes23
5 years, 2 months agotcroots19
5 years, 3 months agomoehijawe
5 years, 5 months ago