Database Reference
In-Depth Information
The result should appear as in Figure 8-6.
Figure 8-6.
First operation failed, second operation rolled back
In the Messages pane shown in Figure 8-6, note that the entire transaction was rolled back because
the
INSERT
failed and was terminated with error number 2627 (whose error message appears at the top of
the window). The
DELETE
error number was 0, meaning it executed successfully but was rolled back. (If
you check the table, you'll find that Spark still exists in the Person table.)
How It Works
Since Pearl already exists and the Person table's PersonID column is the primary key and can contain
only unique values, SQL Server prevents the insertion of a duplicate, so the first operation fails. The
second
DELETE
statement in the transaction is executed, and Spark is deleted since it doesn't have any
child records in the PersonDetails table; but because
gmaxerr
isn't 0 (it's 2627, as you see in the Results
pane), you roll back the transaction by undoing the deletion of Spark. As a result, you see all the records
in the table as it is.
Try It: What Happens When the Second Operation Fails
In this example, you'll insert a valid new person and try to delete a person who has child records in the
PersonDetails table.
Add ag and delete Vidvr by entering the following statement, and then click the Execute button:
exec sp_Trans_Test 'ag', 'Agarwal ',null, 'Vidvr'
