You work as a SQL Server 2012 database developer at ABC.com. You are developing a stored
procedure that updates rows in several tables.
You want the entire transaction to be rolled back should the stored procedure cause a run-time
error.
How would you accomplish this?
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.
F.
You should make use of RAISERROR in the stored procedure.
Explanation:
Ref: http://msdn.microsoft.com/en-us/library/ms188792
a
SET XACT_ABORT
Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error
SYNTAX
SET XACT_ABORT { ON | OFF }
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.
FOR MORE INFORMATION READ MSDN
https://msdn.microsoft.com/en-us/library/ms188792.aspx