SQL Server中的循环中的交易

Question

回滚时发生错误：

msg 6401，第16级，状态1，过程our_trigger，第76行
无法回滚T1。找不到该名称的交易或保存点。

这是触发器中的SQL代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [DOCSADM].[our_trigger]
ON [DOCSADM].[PROFILE]
FOR UPDATE, INSERT
AS
    DECLARE @DocSystemId AS INTEGER
    DECLARE @docNumber AS INTEGER
    DECLARE @lastUsedSystemId AS INTEGER
    DECLARE @itemType AS VARCHAR(1) --dm10 
    DECLARE @manualBarcode AS VARCHAR (50)
    DECLARE @oldBarcodeFK AS INTEGER
    --user typed barcode, it must be inserted/UPDATED into pd_barcode table
    DECLARE @COUNTEXISTINGBARCODE AS INTEGER
    SET nocount ON;
    BEGIN
        DECLARE activity_cursor CURSOR local FOR
            SELECT 
                system_id, docnumber,
                a_doc_barcode, pd_doc_barcode, item_type
            FROM  
                inserted
        --find the last used systemid  
        SELECT @lastUsedSystemId = lastkey
        FROM docsadm.seq_systemkey
        OPEN activity_cursor
        FETCH next FROM activity_cursor INTO @DocSystemId, @docNumber, @manualBarcode, @oldBarcodeFK, @itemtype
        WHILE (@@fetch_status <> -1)
        BEGIN
            IF (( @itemType = 'M' OR @itemType = 'P' ))
                --FIND IF IT EXISTS ALREADY A BARCODE
                SELECT @COUNTEXISTINGBARCODE = COUNT(*)
                FROM docsadm.pd_barcode
                WHERE pd_barcode = @manualBarcode
                IF (@COUNTEXISTINGBARCODE = 0)-- THERE IS NO EXISTING BARCODE
                     DECLARE @barcodeSystemId AS INTEGER = 0
                BEGIN TRANSACTION t1
                BEGIN TRY
                    -- get next sys id    
                    EXECUTE [DOCSADM].[Sp_nextkey] 'SYSTEMKEY'
                    SELECT @barcodeSystemId = lastkey
                    FROM docsadm.seq_systemkey
                    INSERT INTO docsadm.pd_barcode
                    VALUES (@manualBarcode, @barcodeSystemId, 'D', NULL, NULL, 'Y', NULL, NULL)
                    UPDATE docsadm.profile
                    SET pd_doc_barcode = @barcodeSystemId
                    WHERE docnumber = @docNumber
                    COMMIT TRANSACTION t1
                END TRY
                BEGIN CATCH
                    ROLLBACK TRANSACTION t1
                END CATCH
            END
            IF (@COUNTEXISTINGBARCODE <> 0)
               --YES THERE IS AT LEAST ONE BARCODE
            BEGIN
                SELECT TOP 1 @barcodeSystemId = system_id
                FROM docsadm.pd_barcode
                WHERE pd_barcode = @manualBarcode
                BEGIN TRANSACTION t1
                BEGIN TRY
                    --update profile's new barcode reference
                    UPDATE docsadm.profile
                    SET pd_doc_barcode = @barcodeSystemId
                    WHERE docnumber = @docNumber
                    UPDATE docsadm.pd_barcode
                    SET pd_doc_bcode_used = 'Y'
                    WHERE system_id = @barcodeSystemId
                    IF (@oldBarcodeFK <> 0)
                    BEGIN
                        --update old barcode as not used!
                        UPDATE docsadm.pd_barcode
                        SET pd_doc_bcode_used = 'N'
                        WHERE system_id = @oldBarcodeFK
                    END
                    COMMIT TRANSACTION t1
                END TRY
                BEGIN CATCH
                    ROLLBACK TRANSACTION t1
                END CATCH
                END
                FETCH next FROM activity_cursor INTO @DocSystemId, @docNumber, @manualBarcode, @oldBarcodeFK, @itemtype
            END
            CLOSE activity_cursor
            DEALLOCATE activity_cursor
      END

错误来自这里

  BEGIN CATCH
      ROLLBACK TRANSACTION t1
  END CATCH

我试图保存trans，但我遇到了同样的错误。我也开始在循环之前开始跨性别，但发生了消息。

Answer 1

此错误说您尝试提交或回滚不存在的事务。因此，在回滚检查是否存在未解决的交易之前。像这样

IF @@TRANCOUNT>0
ROLLBACK TRANSACTION