You work as a SQL Server 2012 database developer at ABC.com. TesABCig.com has a database
named DataDB.
You are developing a complex stored procedure named sp_updater that will use a single
transaction to update several tables in the DataDB database.
You are concerned about data integrity and incomplete updates should the sp_updater stored
procedure cause a run-time error.
To mitigate this potential problem you want the transaction to terminate and the transaction to be
rolled back if the sp_updater stored procedure raises a run-time error.
How would you accomplish this task?

A.
You should make use of the SET XACT_ABORT ON statement in the stored procedure.

B.
You should have the stored procedure run in the SERIALIZABLE ISOLATION LEVEL.

C.
You should make use of a LOOP hint in the stored procedure.

D.
You should have the stored procedure run in the SNAPSHOT ISOLATION LEVEL.

E.
You should make use of an INSTEAD OF UPDATE trigger in the stored procedure.

Explanation:

Ref: http://msdn.microsoft.com/en-us/library/ms188792