Understanding Transactions - Beginning C# 2008 Databases: From Novice to Professional - page 149

Try It Out: What Happens When the Second Operation Fails
In this example, you'll insert a valid new customer and try to delete a customer who has
child records in Orders table.
Add customer “aaa” and delete customer ALFKI by entering the following statement,
and then click the Execute button.
exec sp_Trans_Test 'aaa', 'aaa ', 'ALFKI'
The result should appear as in Figure 8-5.
Figure 8-5. First operation rolled back.
In the Messages window shown in Figure 8-5, note that the transaction was rolled
back because the DELETE failed and was terminated with error number 547 (the message
for which appears at the top of the window). The INSERT error number was 0, so it appar-
ently executed successfully but was rolled back. (If you check the table, you'll find “aaa”
is not a customer.)
How It Works
Since customer “aaa” doesn't exist, SQL Server inserts the row, so the first operation
succeeds. When the second statement in the transaction is executed, SQL Server pre-
vents the deletion of customer ALFKI because it has child records in the Orders table,
but since @maxerr isn't zero (it's 547, as you see in the Results pane), the entire trans-
action is rolled back.
Try It Out: What Happens When Both Operations Fail
In this example, you'll try to insert an invalid new customer and try to delete an
undeletable one.