What should you do?

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. An application contains two stored procedures. The tasks performed by the stored procedures are as shown in the following table.

You discover that the procedures occasionally throw foreign key violation errors:
* IncludeDetails throws an error when it inserts records into the ProductInfo table.
* ImportNewProducts throws an error when it inserts records into the ProductCurrentPrice table.
You must make sure that the two requirements below can be met:
* If an error occurs in the INSERT statement of ProductInfo, records inserted into ProductHeader and ProductCurrentPrice are committed.
* If an error occurs in the INSERT statement of ProductCurrentPrice, all transactions are rolled back.
What should you do?

A.
Add a SET XACT_ABORT OFF statement in IncludeDetails.
Add a SET XACT_ABORT ON statement in ImportNewProducts.

B.
Add a SET XACT_ABORT ON statement in IncludeDetails.
Add a SET XACT_ABORT OFF statement in ImportNewProducts.

C.
Enclose all statements of IncludeDetails in a TRY/CATCH block.
Add a ROLLBACK TRANSACTION statement in the CATCH block.

D.
Enclose all statements of ImportNewProducts in a TRY/CATCH block.
Add a ROLLBACK TRANSACTION statement in the CATCH block.

Explanation:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.