You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a stored procedure that is created by using the following DDL
code segment. You discover that the UPDATE statement occasionally throws an exception
that causes the entire transaction to roll back. You need to ensure that when the UPDATE
statement causes an exception, SP1 performs the following tasks: • The INSERT statement
is committed, • All data is in a consistent state. • The Transaction count is equal to 0 after
execution completes. What should you do?

A.
Add an XACT_ABORT ON statement to the first line of the procedure.

B.
• Add a SAVE TRANSACTION TR1 statement after the INSERT statement. • Add a
TRY/CATCH block after the SAVE TRANSACTION TR1 statement. • Place the UPDATE
statement inside the new TRY block. • Add a ROLLBACK TRANSACTION TR1 statement
inside the new CATCH block.

C.
• Add a TRY/CATCH block after the INSERT statement. • Place the UPDATE statement
inside the new TRY block. • Add a ROLLBACK TRANSACTION statement inside the new
CATCH block.

D.
• Add a TRY/CATCH block after the INSERT statement. • Place the UPDATE statement
inside the new TRY block. • Add error-handling code inside the new CATCH block.