SQL Server-如何在可能发生ROLLEDBACK的运行进程中创建/COMMIT日志记录

我在尝试复制日志记录时遇到了很多困难,就像我在Oracle中使用PRAGRMA AUTOMUS_TRANSACTION所做的那样,它允许您将记录提交到日志表中,而不提交任何其他DML操作。我一直在想,更有经验的SQL Server人员是如何记录他们的数据库程序/进程的成功或错误的。特别是有经验的T-SQL人员是如何在活动的T-SQL程序中登录的?换一种说法……我有一个巨大的进程,在整个进程没有严重错误的情况下执行之前,不能提交,但我仍然需要记录所有错误,如果是严重错误,则回滚整个进程,但我仍需要日志。

以MERGE为例,展示我无法提交某些记录,但回滚其他记录。。。

我在下面的脚本中有两个命名的交易(1。源数据,2。主进程)。。。这是我第一次尝试实现这个目标。首先,脚本使用第一个事务将记录插入到一个没有COMMIT的表中。然后在MERGE块的事务2中,我将记录插入到Destination表和Log表中,并提交事务2(mainProcess)。

然后,我将回滚第一个命名的事务(sourceData)。。

问题是……尽管我明确提交了mainProcess事务,但一切都被回滚了。

GO
/* temp table to simulate Log Table */
IF OBJECT_ID('tempdb..#LogTable') IS NULL
    CREATE TABLE #LogTable  
    (   Action                  VARCHAR(50),
        primaryID               INT,
        secondaryID             INT,
        CustomID                INT,
        Note                    VARCHAR(200),
        ConvDate                DATE
    ) --DROP TABLE IF EXISTS #LogTable;
;  /* SELECT * FROM #LogTable; TRUNCATE TABLE #LogTable; */

/* SELECT * FROM #ProductionSrcTable */
IF OBJECT_ID('tempdb..#ProductionSrcTable') IS NULL
    CREATE TABLE #ProductionSrcTable(   primaryKey INT, contactName VARCHAR(200), sourceKey    INT  )
; --DROP TABLE IF EXISTS #ProductionSrcTable; TRUNCATE TABLE #ProductionSrcTable;

/* SELECT * FROM #ProductionDestTable */
IF OBJECT_ID('tempdb..#ProductionDestTable') IS NULL
    CREATE TABLE #ProductionDestTable(  primaryKey INT, contactName VARCHAR(200), secondaryKey INT  )
; --DROP TABLE IF EXISTS #ProductionDestTable; TRUNCATE TABLE #ProductionDestTable;

GO

/* Insert some fake data into Source Table */
BEGIN TRAN sourceData

BEGIN TRY
    INSERT INTO #ProductionSrcTable
        SELECT 1001 AS primaryKey, 'Jason' AS contactName, 789105 AS sourceKey UNION ALL
        SELECT 1002 AS primaryKey, 'Jane'  AS contactName, 789185 AS sourceKey UNION ALL
        SELECT 1003 AS primaryKey, 'Sam'   AS contactName, 788181 AS sourceKey UNION ALL
        SELECT 1004 AS primaryKey, 'Susan' AS contactName, 681181 AS sourceKey
    ;
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION sourceData
END CATCH

/* COMMIT below is purposely commented out in order to Test */
--COMMIT TRANSACTION sourceData

GO

BEGIN TRAN mainProcess

    DECLARE @insertedRecords    INT = 0,
            @CustomID           INT = 2,
            @Note               VARCHAR(200) = 'Test Temp DB Record Population via OUTPUT Clause',
            @ConvDate           DATE = getDate()
    ;

BEGIN TRY

    MERGE INTO #ProductionDestTable AS dest
    USING 

    (
        SELECT src.primaryKey, src.contactName, src.sourceKey FROM #ProductionSrcTable src

    ) AS src ON src.primaryKey = dest.primaryKey AND src.sourceKey = dest.secondaryKey

    WHEN NOT MATCHED BY TARGET 
    THEN
        INSERT --INTO ProductionDestTable
        (   primaryKey, contactName, secondaryKey               )
        VALUES
        (   src.primaryKey, src.contactName, src.sourceKey      )


    /* Insert Output in Log Table  */
    OUTPUT $action, inserted.primaryKey, src.sourceKey, @CustomID, @Note, @ConvDate INTO #LogTable;


    ; /* END MERGE */

    /* Store the number of inserted Records into the insertedRecords variable */
    SET @insertedRecords = @@ROWCOUNT;

    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION mainProcess
    END CATCH
;

--ROLLBACK TRANSACTION mainProcess
COMMIT TRANSACTION mainProcess

ROLLBACK TRANSACTION sourceData

PRINT 'Records Inserted:' + CAST(@insertedRecords AS VARCHAR);

/* END  */

--SELECT @@TRANCOUNT