Geek asks and answers

ROLLBACK TRANSACTION request does not have a corresponding BEGIN TRANSACTION error in sql server

I get the error message "ROLLBACK TRANSACTION request does not have a corresponding BEGIN TRANSACTION". I am trying to cancel a transaction if the number of rows for any delete operator is zero. Below is my code. What am I doing wrong? Please, help

alter procedure delete_staff(@staffID varchar(10))
as
declare @tempvar varchar(50), @staffName varchar(50), @jobTitle varchar(50), @dept varchar(50)
begin transaction trans1
        declare @rc1 int
        declare @rc2 int
        declare @rc3 int
        select @tempvar = left(@staffID,1) from Staff
        delete from staff where staffID = @staffID
        set @rc1=@@rowcount
        delete from Login where userID = @staffID 
        set @rc2=@@rowcount
        begin
        if(@tempvar='S')
                begin
                delete from Specialist where specialistID = @staffID    
                set @rc3=@@rowcount
                end
        else if(@tempvar='H')
                begin
                delete from Helpdesk_Operator where helpdesk_OperatorID = @staffID
                set @rc3=@@rowcount
                end
        commit transaction trans1
        end
        if(@rc1=0 or @rc2=0 or @rc3=0)
        begin
        rollback transaction trans1
        end
+5
source share
4 answers

If you make a transaction, you cannot roll back. Do one or the other:

if(@rc1=0 or @rc2=0 or @rc3=0)
begin
  rollback transaction trans1
end else begin
  commit transaction trans1
end
+5
source

You have commit transaction trans1right in front of your if statement to rollback. A transaction will always be executed before you check the counters.

+4
source

, commit transaction trans1 , .

+1

This happens if your transaction has already been completed before you actually go into your commit statement. You can give the condition "If (@@ TRANCOUNT> 0)" before the expression "COMMIT TRANSACTION".

For instance:

 BEGIN TRANSACTION
  SELECT 0--Statements To Excecute
  ROLLBACK
  IF(@@TRANCOUNT>0)
  COMMIT TRANSACTION

OR

  BEGIN TRY
  BEGIN TRANSACTION
  SELECT 0 --Statements To Excecute     
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF(@@TRANCOUNT>0)
  ROLLBACK
  END CATCH
+1
source

More articles:


All Articles