You are a database developer on an instance of SQL Server 2008. You want to write Transact-SQL code to modify data in two separate tables, the Inventory table and the InvHistory table. First, the Inventory table should be updated. If the update to the Inventory table is successful, then a row should be inserted into the InvHistory table.
If either of the modifications fails, both should be rolled back.
What should you do?
A. Set the XACT_ABORT option to OFF and enclose the code within a single explicit transaction.
B. Include the code for both DML operations in a TRY block, and include a CATCH block that includes a ROLLBACK TRANSACTION statement.
C. Issue the UPDATE and INSERT statements, then check @@ERROR and roll back the transaction if @@ERROR returns a non-zero value.
D. Set the XACT_ABORT option to ON and enclose the code within a single explicit transaction.
