ROLLBACK TRANSACTION请求在sql server中没有相应的BEGIN TRANSACTION错误

I am getting the error "ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". I am trying to rollback the transaction if the row count for any delete statement is zero. Given below is my code. What am I doing wrong? Please help

我收到错误“ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION”。如果任何delete语句的行计数为零，我试图回滚事务。以下是我的代码。我究竟做错了什么？请帮忙

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

4 个解决方案

#1

If you commit the transaction, you can't then make a rollback. 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

#2

You have commit transaction trans1 right before your if statement for the rollback. The transaction will always be committed before you check the counts.

您已在if语句之前提交事务trans1以进行回滚。在您检查计数之前，将始终提交交易。

#3

I believe the commit transaction trans1 is always getting hit, therefore you will be unable to rollback from that point.

我相信提交事务trans1总是被命中，因此您将无法从该点回滚。

#4

This happens if your transaction has already been committed before you actually go into your commit statement. You might give a condition 'If (@@TRANCOUNT>0)' before your 'COMMIT TRANSACTION' Statement.

如果您的事务在实际进入commit语句之前已经提交，则会发生这种情况。您可以在'COMMIT TRANSACTION'语句之前给出条件'If（@@ TRANCOUNT> 0）'。

For Eg:

对于Eg：

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

要么

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

#1