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
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Products …
UPDATE Sales…
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH;
RETURN
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 commited.
• All data is in a consistent state.
• The Transaction count is equal to 0 after execution completes.
What should you do?
A. • Add a SAVE TRANSACTION TR1 statement after the INSERT statement.
• Add a TRY/CATCH block after the SAVE TANSACTION TR1 statement.
• Place the Update statement inside the TRY block.
• Place the ROLLBACK TRANSACTION TR1 inside the CATCH block.
B. • Add SET XACT_ABORT ON statement to the first line if the stored procedure.
C. • Add a TRY/CATCH block after the INSERT statement.
• Place the Update statement inside the TRY block.
• Place the ROLLBACK TRANSACTION inside the CATCH block.
D. • Add a TRY/CATCH block after the INSERT statement.
• Place the Update statement inside the TRY block.
• Place error handling code in the CATCH block.
