Database Reference
In-Depth Information
-- If an error occurred, roll back
if @maxerr <> 0
begin
rollback
print 'Transaction rolled back'
end
else
begin
commit
print 'Transaction committed'
end
Tip
T-SQL (and standard SQL) supports various alternative forms for keywords and phrases. You've used just
ROLLBACK
and
COMMIT
here.
Then you add some more instrumentation so you can see what error numbers are encountered
during the transaction.
print 'INSERT error number:' + cast(@inserr as nvarchar(8))
print 'DELETE error number:' + cast(@delerr as nvarchar(8))
return @maxerr
Now let's look at what happens when you execute the stored procedure. You run it twice, first by
adding Pearl and next by adding Spark, but you also enter the same nonexistent person Agarw to delete
each time. If all statements in a transaction are supposed to succeed or fail as one unit, why does the
INSERT
succeed when the
DELETE
doesn't delete anything?
Figure 8-4 should make everything clear. Both the
INSERT
and
DELETE
return error number 0. The
reason
DELETE
returns error number 0 even though it has not deleted any rows is that when a
DELETE
doesn't find any rows to delete, T-SQL doesn't treat that as an error. In fact, that's why you use a
nonexistent person. Excluding these recently added people Pearl and Spark, other records have child
records in the PersonDetails table, as shown in Figure 8-3; therefore, you can't delete the existing people
unless you delete their details from the PersonDetails table first.
Try It: What Happens When the First Operation Fails
In this example, you'll try to insert a duplicate person and delete an existing person. Add Pearl and
delete Spark by entering the following statement, and then click the Execute button:
exec sp_Trans_Test 'Pearl', 'Vamika', null,'Spark'
