sql-server - 提交事务后更新表，但更新失败需要回滚更新和提交的插入

Question

我有一个允许用户提交申请表的 C# 应用程序。该应用程序要求提供父母姓名以及他们是否已婚。提交后，将为每个父级创建一个新的用户记录。如果父母已婚，我需要用另一位父母的 ID 更新每个新用户记录中的 SpouseID 字段。请参见下面的示例表：

ID 名 姓 配偶 ID
---------------------------------
1约翰史密斯2
2珍妮史密斯1
3 吉姆·杰克逊 NULL

目前，我的代码包含在开始尝试语句中，因此在提交插入语句之前，我无法使用新创建的用户 ID 更新 SpouseID 字段。我可以将更新 SpouseID 字段的语句移动到 COMMIT TRANSACTION 语句之后，但如果更新 SpouseID 字段出现问题，我仍然希望能够回滚所有内容。

这可能吗？任何有关如何解决此问题的想法将不胜感激。

我已经包含了存储过程代码的简化版本。

SET XACT_ABORT ON

BEGIN TRY

    SET @OuterTransFlag = @@TRANCOUNT

    IF @OuterTransFlag = 0 
        BEGIN TRANSACTION

            IF (@UserIDP1 = 0) 
                BEGIN
                    INSERT INTO Users (Surname, Given1)
                    SELECT ISNULL(Surname, ''), ISNULL(GivenName, '')
                    FROM ParentInfo_Processed
                    WHERE CODE = @Code 

                    SET @Parent1ID = SCOPE_IDENTITY()
                END 

            IF (@UserIDP2 = 0) 
                BEGIN
                    INSERT INTO Users (Surname, Given1)
                    SELECT ISNULL(Surname, ''), ISNULL(GivenName, '')
                    FROM ParentInfo_Processed
                    WHERE CODE = @Code 

                    SET @Parent2ID = SCOPE_IDENTITY()
                END 

        IF (@ParentStatus = 'M')
            BEGIN                                           
                UPDATE Users 
                SET SpouseID = @Parent1ID 
                WHERE ID = @Parent2ID   

                UPDATE Users 
                SET SpouseID = @Parent2ID
                WHERE ID = @Parent1ID   
            END

    IF @OuterTransFlag = 0 AND @@TRANCOUNT <> 0
        COMMIT TRANSACTION

END TRY

BEGIN CATCH
    DECLARE @em VARCHAR(2000)
    SET     @em = ERROR_MESSAGE()

    IF @OuterTransFlag = 0 AND @@TRANCOUNT <> 0
        ROLLBACK TRANSACTION

    IF ISNULL(@em, '') <> ''
        RAISERROR(@em, 16, 1)
        RETURN(-1)
END CATCH

标签： sql-serverstored-procedurestransactionssql-updatesql-insert