为什么我的 SQL Server 审核触发器会扰乱来自 Access 的 OBDC 调用/刷新？

我在其中一个表上实现了一个审核触发器，它基本上将旧记录和新记录以及日期和用户复制到名为 ..._Audit 的表中。我将在下面发布我的脚本。

问题是，当我在 Access 中插入新记录然后按 Tab 进行切换时，它会刷新并显示表中的第一条记录。下面是一个例子 - 我添加了前三个记录，然后刷新，然后我添加了另外三个完全相同的数据 添加它们后，我应该看到具有相同数据和递增 ID 的记录，但相反，它们抓住了第一个记录该表的三条记录为最后三条记录。

 P_SubtaskID  PresetID_FK P_SubtaskName            DateDay DateMonth
 148          17          a new subtask            1       7
 149          17          a new subtask            1       7
 150          17          a new subtask            1       7
 8            5           Receive, sign and save   25      10
 9            5           Electronic lodgement     30      10
 10           1           Review                   12      7

点击刷新后，这些记录就会显示它们应该显示的内容：

 P_SubtaskID  PresetID_FK P_SubtaskName            DateDay DateMonth
 148          17          a new subtask            1       7
 149          17          a new subtask            1       7
 150          17          a new subtask            1       7
 151          17          a new subtask            25      10
 152          17          a new subtask            30      10
 153          17          a new subtask            12      7

这是有问题的，因为当用户添加记录然后保存它时，它会显示完全不相关的记录。如果他们对此幻影进行更改，则会影响该记录 - 这很容易导致错误更新/删除记录等。不好！

这是我创建审计表及其关联触发器的脚本：

USE [ClientDatabase]
 GO

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 DROP TABLE [dbo].[AutoTaskPresets_Subtasks_Audit]
 GO

 CREATE TABLE [dbo].[AutoTaskPresets_Subtasks_Audit](
    SessionID int identity(1,1) not null,
    [P_SubtaskID] [int] NULL,
    [PresetID_FK] [int] NULL,
    [P_SubtaskName] [nvarchar](255) NULL,
    [DateDay] [int] NULL,
    [DateMonth] [int] NULL,
    [DatePeriod] [int] NULL,
    [StaffName_FK] [nvarchar](255) NOT NULL,
    Action nchar(10) null,
    RowType nchar(10) null,
    ChangedDate datetime not null default getdate(),
    ChangedBy sysname not null default user_name()
 )
 GO

 CREATE Trigger [dbo].[DeleteAutoTaskPresets_Subtasks] ON [dbo].
     [AutoTaskPresets_Subtasks] FOR DELETE AS  

 BEGIN  
     SET NOCOUNT ON
     INSERT dbo.AutoTaskPresets_Subtasks_Audit(
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
        [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        Action,
        RowType)
     SELECT
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
        [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        'Deleted',
        'Old'
    FROM Deleted
 END
 GO

 CREATE Trigger [dbo].[InsertAutoTaskPresets_Subtasks]
    ON [dbo].[AutoTaskPresets_Subtasks] FOR INSERT AS  
 BEGIN  
     SET NOCOUNT ON
     INSERT dbo.AutoTaskPresets_Subtasks_Audit(
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
    [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        Action,
        RowType)
     SELECT
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
        [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        'Inserted',
        'New'
    FROM Inserted

 END
 GO

 CREATE Trigger [dbo].[UpdateAutoTaskPresets_Subtasks]
ON [dbo].[AutoTaskPresets_Subtasks] FOR UPDATE AS  
 BEGIN  
     SET NOCOUNT ON
     INSERT dbo.AutoTaskPresets_Subtasks_Audit(
        [P_SubtaskID],
    [PresetID_FK],
    [P_SubtaskName],
    [DateDay],
    [DateMonth],
    [DatePeriod],
    [StaffName_FK],
    [Action],
    RowType)
     SELECT
    [P_SubtaskID],
    [PresetID_FK],
    [P_SubtaskName],
    [DateDay],
    [DateMonth],
    [DatePeriod],
    [StaffName_FK],
    'Updated',
    'Old'
 FROM Deleted

     INSERT dbo.AutoTAskPresets_Subtasks_Audit(
    [P_SubtaskID],
    [PresetID_FK],
    [P_SubtaskName],
    [DateDay],
    [DateMonth],
    [DatePeriod],
    [StaffName_FK],
    [Action],
    RowType)
     SELECT
    [P_SubtaskID],
    [PresetID_FK],
    [P_SubtaskName],
    [DateDay],
    [DateMonth],
    [DatePeriod],
    [StaffName_FK],
    'Updated',
    'New'
FROM Inserted

 END
 GO

我很困惑为什么会发生这种情况。我能想到的也许是一个时间问题，尽管目前触发器在我认为是首选的操作之后运行。

我确信这是我的触发器，因为当我删除它们时，它工作得很好。我的数据库也从未出现过这样的错误。

我正在使用 SQL Server 2005，并通过 ODBC 连接到 MS Access 2007 客户端。

正如 Nikola 所解释的，该问题与 @@identity 的使用有关。这采用最后一个记录条目的标识值，因此当我执行插入时，触发器会插入到不同的表中。这会导致 @@identity 的值不同，而 MS Access 似乎错误地依赖了该值。为了解决这个问题，我需要在运行触发器之前保存 @@identity 值，然后恢复它。插入触发器如下图所示：

 CREATE Trigger [dbo].[InsertAutoTaskPresets_Subtasks]
    ON [dbo].[AutoTaskPresets_Subtasks] FOR INSERT AS  
 BEGIN  
     SET NOCOUNT ON

     declare @id int
     set @id = @@identity

     INSERT dbo.AutoTaskPresets_Subtasks_Audit(
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
    [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        Action,
        RowType)
     SELECT
        [P_SubtaskID],
        [PresetID_FK],
        [P_SubtaskName],
        [DateDay],
        [DateMonth],
        [DatePeriod],
        [StaffName_FK],
        'Inserted',
        'New'
    FROM Inserted

DECLARE @SQL varchar(8000)
SET @sql = 'SELECT IDENTITY(INT, ' + CAST(@id as varchar) + ', 1)
         AS ident INTO #Tmp'
EXEC(@sql)

 END
 GO

遗憾的是 Access 似乎是以这种方式构建的，依赖于并非 100% 准确的东西。

最后，我只在插入触发器上实现了这个，即使其他触发器使用插入操作。经过一些简短的测试后，这似乎没问题。我现在已将此脚本推广到所有可由普通用户编辑的表上，因此不久之后我就会知道这是否会导致问题。