Question

我收到错误“ ROLBACK 传输请求没有相应的 BEGIN 传输请求 ” 。如果任何删除语句的行数为零, 我正试图将交易推回。如下为我的代码。我做错了什么? 请帮助。

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

Answer 1

如果您进行交易, 无法进行回滚。执行一个或另一个 :

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

Answer 2

您有 进行交易 Trans1 , 如果您要回滚的话, 则在您之前先进行。交易总是在您检查计数前进行。

Answer 3

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.

类型 :

 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

Answer 4

我相信 进行交易转1 总是被击中, 因此您无法从那个角度退缩。

友情链接