You are a database developer on an instance of SQL Server 2008. The Donation table in your database was created using the following statement:
CREATE TABLE Donation (
DonationID int PRIMARY KEY,
DonorID int,
PledgeAmt money CHECK (PledgeAmt > 0),
PledgeDate datetime DEFAULT GETDATE());
The Donation table currently contains no data.
You execute the following Transact-SQL in SQL Server Management Studio:
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO Donation VALUES(2, 1, 500, ’12-01-2008′);
INSERT INTO Donation VALUES(1, 3, 0, ’12-15-2008′);
INSERT INTO Donation VALUES(3, 7, 250, DEFAULT);
COMMIT TRANSACTION
What is the result?
A. The Transact-SQL executes successfully and inserts two rows into the Donation table.
B. The Transact-SQL executes successfully and inserts three rows into the Donation table.
C. The Transact-SQL generates an error message, but inserts two rows into the Donation table.
D. The Transact-SQL generates an error message and rolls back all inserts.
Correct Answer: D
Explanation/Reference:
When XACT_ABORT is set to ON, and a Transact-SQL statement raises an error at run time, the entire transaction is terminated and rolled back. When the XACT_ABORT option is set to OFF, only the Transact-SQL statement that raised the error is rolled back. The remaining statements in the transaction will be executed. The default value of the XACT_ABORT option is OFF. In this scenario, the second INSERT statement fails because it violates the CHECK constraint defined on the PledgeAmt column. The statement returns the following error message:
The INSERT statement conflicted with the CHECK constraint "CK__Donation__Pledge__797309D9". The conflict occurred in database "KIT3", table "dbo.Donation", column ‘PledgeAmt’.
The entire transaction is rolled back, and no rows are inserted into the Donation table. If the SET XACT_ABORT ON statement had been omitted or SET
XACT_ABORT OFF had been specified, the two successful inserts would have been performed, and only the second insert would have been rolled back. The resulting table would be as follows:
All of the other options are incorrect because the entire transaction is rolled back.