Error Trapping / Rollbacks

Question

Post reply

Error Trapping / Rollbacks

rebecca rincon

SSC Rookie

Points: 37
More actions
October 18, 2006 at 10:23 am

#90743

I am trying to figure out why my transaction will rollback with statement A below but not when I run EXAMPLE B below. I've read that severity levels of errors come into play, but both errors register with the same severity level - 16. Anyone got any ideas? Thanks.
EXAMPLE A:
begin transaction
insert into tblABC select
'1654651',
'rincon',
'rebecca',
'3532 fake street',
'san francisco',
'ca'

update tblABC set ad='6546543'
--this is a bad transaction. no field called "ad" exists in tblABC

if @@error <>0 rollback transaction

commit transaction

EXAMPLE B:
begin transaction
insert into tblABC select
'1654651',
'rincon',
'rebecca',
'3532 fake street',
'san francisco',
'ca'

update tblABCX set ad='6546543'
--there is no tblABCX in the catalog

if @@error <>0 rollback transaction

commit transaction

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

Interesting...

With SQL2000:

In Example A the batch actually fails to compile so the transaction does not even begin. This is what I would expect to happen.

In Example B the batch does compile and starts the transaction. When the invalid object is found, the batch terminates without a rollback even with SET XACT_ABORT ON. This may be a bug or it may allow for temporary tables being created in a batch. While this situation is unlikely to occur in production code it is worth knowing about.

rebecca rincon SSC Rookie Points: 37 More actions · Answer 2

Thanks for the reply. The issue I'm trying to avoid is allowing the transaction to insert if someone goes into the procedure and changes the table name on the update statement. The problem we're having is that the rows are being inserted but the update is not happening so we've ended up with a ton of duplicates.

I know this is a user issue and should not be addressed with error handling, but do you know any other way to rollback a transaction if an object cannot be found in the db, or will it always fail no matter what?

Thanks again.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 3

I am not sure why you want to do an INSERT followed by an UPDATE, but your second example is always going to fail.

To stop users changing your SPs you could try:

1. Restricting the rights of your users in the production environment.

2. Using CREATE PROCEDURE ... WITH ENCRYPTION in the production environment.

If you do not control the production environment and your users are intent on changing your SPs, they will be able to. This leaves:

1. Charge them a small forture for support.

2. Do not use SPs but do everything with dynamic SQL from your application. Doing this with SP_EXECUTESQL will help mitigate the performance issues.

rebecca rincon SSC Rookie Points: 37 More actions · Answer 4

I don't rightly know either why we would have an update after an insert. I appreciate the information and I will pass it onto the people that pay me. Thanks.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 5

Thinking about it, the second example will neither rollback nor commit. This means that when the session finishes, the INSERTs to the first table should be rolled back unless they are committed elsewhere.