Access BeforeUpdate取消更新后SQL Server触发器仍触发求助

我使用Access作为前端，连接SQL Server数据库的链接表。其中tbl_Review表设有触发器，当AllocatedUser列被更新时触发。Access表单绑定了该字段的控件，控件的VBA BeforeUpdate事件会验证变更的有效性，若无效则取消Update事件，但即便Update事件已被正确取消，SQL Server端的触发器仍会执行。

相关代码 #

VBA BeforeUpdate事件代码 #

Private Sub AllocatedUser_BeforeUpdate(Cancel As Integer)
    If gErrorHandling Then On Error GoTo My_Error
        
        Dim lngCheck As Long
        
        '获取主客户组内所有审核的最高状态ID
        lngCheck = RunStoredProcRV("sp_Review_Allocate_Check", Me.MasterID)
    
        If lngCheck >= 1040 Then
            Cancel = True
            Me.AllocatedUser.Undo
            MsgBox "无法更改审核人，该主客户组下已有一个或多个审核已完成。", vbCritical, "审核已完成"
        ElseIf lngCheck >= 1020 Then
            If MsgBox("该主客户组下已有一个或多个审核已开始。" & vbCrLf & vbCrLf & _
                      "确定要更改审核人吗？", vbQuestion + vbYesNo + vbDefaultButton2, _
                      "审核进行中 - 更改审核人？") = vbNo Then
                Cancel = True
                Me.AllocatedUser.Undo
            End If
        End If
    
    My_Exit:
        Exit Sub
        
    My_Error:
        MsgBox Err.Number & vbCrLf & Err.Description
        Resume My_Exit
    
End Sub

SQL Server触发器代码 #

ALTER TRIGGER [dbo].[trg_Review_Allocate_on_Master]
ON [dbo].[tbl_Review] 
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    IF UPDATE (AllocatedUser)
    BEGIN
        DECLARE @MasterID int
        DECLARE @AllocatedUser varchar(50)
        DECLARE @AllocatedDate date
        DECLARE @NewStatus int

        -- 获取MasterID和新分配的用户
        SELECT @MasterID = MasterID,
               @AllocatedUser = AllocatedUser,
               @AllocatedDate = AllocatedDate,
               @NewStatus = ReviewStatusID
        FROM inserted i
        INNER JOIN tbl_Customer c ON i.CustomerID = c.CustomerID

        UPDATE tbl_Review
        SET    AllocatedUser = @AllocatedUser,
               AllocatedDate = @AllocatedDate,
               ReviewStatusID = @NewStatus
        FROM tbl_Review r
        INNER JOIN tbl_Customer c ON r.CustomerID = c.CustomerID
        WHERE c.MasterID = @MasterID
          AND c.CustomerID <> @MasterID
    END
END

原因分析 #

Access链接表的控件绑定机制中，当用户修改控件值时，前端会先将变更提交到SQL Server（触发触发器），之后才执行BeforeUpdate事件的取消逻辑并回滚前端显示。这就导致触发器在前端取消更新前已经被执行。

解决方法 #

方法1：改用未绑定控件，手动控制更新流程 #

这是最可靠的方案，完全避免无效的后端提交：

1. 将表单上的AllocatedUser控件设置为未绑定（控件数据源留空）
2. 表单加载时，从链接表读取当前值赋值给未绑定控件
3. 通过按钮触发更新操作，先完成验证，验证通过后再手动执行SQL更新后端表

示例代码：

' 表单加载时初始化未绑定控件值
Private Sub Form_Load()
    Me.txtAllocatedUser = Me.AllocatedUser ' txtAllocatedUser为未绑定控件
End Sub

' 更新按钮点击事件
Private Sub btnSaveAllocatedUser_Click()
    If gErrorHandling Then On Error GoTo My_Error
    
    Dim lngCheck As Long
    lngCheck = RunStoredProcRV("sp_Review_Allocate_Check", Me.MasterID)
    
    ' 验证逻辑
    If lngCheck >= 1040 Then
        MsgBox "无法更改审核人，该主客户组下已有一个或多个审核已完成。", vbCritical, "审核已完成"
        Me.txtAllocatedUser = Me.AllocatedUser ' 恢复原值
        Exit Sub
    ElseIf lngCheck >= 1020 Then
        If MsgBox("该主客户组下已有一个或多个审核已开始。" & vbCrLf & vbCrLf & _
                  "确定要更改审核人吗？", vbQuestion + vbYesNo + vbDefaultButton2, _
                  "审核进行中 - 更改审核人？") = vbNo Then
            Me.txtAllocatedUser = Me.AllocatedUser ' 恢复原值
            Exit Sub
        End If
    End If
    
    ' 验证通过，手动更新后端表
    Dim strSQL As String
    strSQL = "UPDATE dbo.tbl_Review " & _
             "SET AllocatedUser = '" & Replace(Me.txtAllocatedUser, "'", "''") & "' " & _
             "WHERE ReviewID = " & Me.ReviewID ' 假设ReviewID是主键
    
    CurrentDb.Execute strSQL, dbFailOnError
    
    ' 更新表单绑定字段的显示值
    Me.AllocatedUser = Me.txtAllocatedUser
    MsgBox "审核人已更新", vbInformation
    
My_Exit:
    Exit Sub
    
My_Error:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume My_Exit
End Sub

方法2：修改触发器，仅在值实际变更时执行 #

在触发器中增加判断，只有当AllocatedUser的新旧值确实不同时才执行逻辑，可过滤掉无意义的更新：

ALTER TRIGGER [dbo].[trg_Review_Allocate_on_Master]
ON [dbo].[tbl_Review] 
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    -- 同时判断列被更新且新旧值不同
    IF UPDATE(AllocatedUser) AND EXISTS(
        SELECT 1 
        FROM inserted i
        INNER JOIN deleted d ON i.ReviewID = d.ReviewID
        WHERE i.AllocatedUser <> d.AllocatedUser
    )
    BEGIN
        DECLARE @MasterID int
        DECLARE @AllocatedUser varchar(50)
        DECLARE @AllocatedDate date
        DECLARE @NewStatus int

        SELECT @MasterID = c.MasterID,
               @AllocatedUser = i.AllocatedUser,
               @AllocatedDate = i.AllocatedDate,
               @NewStatus = i.ReviewStatusID
        FROM inserted i
        INNER JOIN tbl_Customer c ON i.CustomerID = c.CustomerID

        UPDATE tbl_Review
        SET    AllocatedUser = @AllocatedUser,
               AllocatedDate = @AllocatedDate,
               ReviewStatusID = @NewStatus
        FROM tbl_Review r
        INNER JOIN tbl_Customer c ON r.CustomerID = c.CustomerID
        WHERE c.MasterID = @MasterID
          AND c.CustomerID <> @MasterID
    END
END

注意：此方法无法阻止前端取消更新前的触发器执行，仅能过滤掉值未变化的更新场景，优先级低于方法1。

方法3：使用事务处理回滚后端操作 #

在BeforeUpdate事件中开启事务，验证不通过时回滚事务，确保后端的更新和触发器操作被完全撤销：

Private Sub AllocatedUser_BeforeUpdate(Cancel As Integer)
    If gErrorHandling Then On Error GoTo My_Error
    
    Dim lngCheck As Long
    Dim db As DAO.Database
    Set db = CurrentDb
    
    ' 开启事务
    db.BeginTrans
    
    lngCheck = RunStoredProcRV("sp_Review_Allocate_Check", Me.MasterID)

    If lngCheck >= 1040 Then
        Cancel = True
        db.Rollback ' 回滚事务，撤销后端更新
        Me.AllocatedUser.Undo
        MsgBox "无法更改审核人，该主客户组下已有一个或多个审核已完成。", vbCritical, "审核已完成"
    ElseIf lngCheck >= 1020 Then
        If MsgBox("该主客户组下已有一个或多个审核已开始。" & vbCrLf & vbCrLf & _
                  "确定要更改审核人吗？", vbQuestion + vbYesNo + vbDefaultButton2, _
                  "审核进行中 - 更改审核人？") = vbNo Then
            Cancel = True
            db.Rollback ' 回滚事务
            Me.AllocatedUser.Undo
        Else
            db.CommitTrans ' 提交事务
        End If
    Else
        db.CommitTrans ' 提交事务
    End If

My_Exit:
    Set db = Nothing
    Exit Sub
    
My_Error:
    db.Rollback ' 出错时强制回滚
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume My_Exit
End Sub

注意：需确保SQL Server链接表支持事务，此方案可有效撤销后端操作，但仍会短暂触发触发器，只是最终影响会被回滚。

内容的提问来源于stack exchange，提问作者BiigJiim