Begin/Commit Transaction Question 1

[wesmk]

HELP...
Sure it's something very simple I'm not doing but begin transaction command not working:

begin transaction

insert into tmc_counter values ('','',11)

delete from tmc_counter1
where counter_num = 11

commit transaction

it performs the insert then gives an error for delete because of the misspelled tablename. I thought the whole point of Begin/Commit statement was to all succeed or all fail. Why is it not failing the insert statement as well?

Please help the newbie...thanks

 

[mrdenny]

You would need to rollback the transaction in order for the insert to be rollback.

declare @ERR int
BEGIN TRANSACTION
   insert into tmc_couter 
   values
   ('', '', 11)
   if @@ERROR <> 0
      set @ERR = @@ERROR

   delete from tmc_counter1
   where couter_num = 11
   if @@ERROR <> 0
      set @ERR = @@ERROR

   if @ERR <> 0
      ROLLBACK
   ELSE
      COMMIT

An invalid object message will error out with a severity level of 16. This severity level will stop the batch from completing, so you won't even get to your error checking.

The reason that the record is being written is that you are either closing the connection which will force the commit, or you are running the next select within the same connection context, which means that you are actually running it within the same transaction, so you will see the new record. You can test this by running your code again, getting the error message, then typing in ROLLBACK selecting it, and clicking the run button in Query Analyser. Then run the query to select the data, and you will see that it is not in the table.

The error catching in SQL 2000 is not the greatest. I'm hoping that the error catching in SQL 2005 will be better.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[wesmk]

still didn't work. It's inserting the row and I need it to wait for delete error and both statements fail. Is there some default ms sql uses that I need to turn off or something? thanks...please help

 

[mrdenny]

Like I said above the problem that you are having is that you aren't getting to your error catching because of the severity level of the error that you are getting do to the table name being wrong.

Denny

--Anything is possible. All it takes is a little research. (Me)