You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code
segment. (Line numbers are included for reference only.)
The procedure can be called within other transactions.
You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table
succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?
A.
IF @@TRANCOUNT = 0
B.
IF (XACT_STATE ( ) ) = 0
C.
IF (XACT_STATE ( ) ) = 1
D.
IF @@TRANCOUNT = l
Explanation:
http://msdn.microsoft.com/en-us/library/ms189797.aspx
http://msdn.microsoft.com/en-us/library/ms187967.aspx
?
C
C
For me if could be D as well.. It would mean “If Delete was successfull commit delete …”…
Can anyone give a motivated explanation of selecting C? Thanks
Your answer is in the explanations links…
Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.
Therefore @@TRANCOUNT may have a value = 1, but this does not determine if it is commitable, so you must use C.
Further explanation…
XACT_STATE = 1
The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
= 0 is no active user transaction
= -1 is active transaction, but has an error which makes it uncommittable