Designing Database Solutions for Microsoft SQL Server — Question 17

You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table.
You need to design the stored procedure to meet the following requirements:
If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.
If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.
What should you include in the design?

Answer options

• A. An explicit transaction that has XACT_ABORT disabled
• B. An implicit transaction that has error handling enabled
• C. An explicit transaction that has error handling enabled
• D. An implicit transaction that has XACT.ABORT enabled

Correct answer: C

Explanation

The correct answer is C because an explicit transaction with error handling enabled allows for proper rollback of all changes if a constraint violation occurs, while still logging the error. Option A is incorrect as disabling XACT_ABORT would prevent the transaction from rolling back on an error. Option B does not provide the necessary control over the transaction, and Option D, while enabling XACT_ABORT, does not explicitly handle the error logging requirement.