为什么我的 SQL Server 审计触发器搞乱了 OBDC 调用/从 Access 刷新? [英] Why is my SQL Server auditing trigger messing up OBDC call/refresh from Access?

问题描述

我在我的一个表上实现了一个审计触发器，它基本上将旧记录和新记录连同日期和用户一起复制到一个名为 ..._Audit 的表中.我会进一步发布我的脚本.

I've implemented an auditing trigger on one of my tables, it basically copies the old record and the new record into a table called ..._Audit, along with date and user. I'll post my script further down.

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

The problem is that, when I insert a new record in Access then tab across, it refreshes and shows the first record in the table. An example of this is below - I have added the first three records then refreshed, then I added three more of the exact same data After adding them I should be seeing records with the same data and incremented IDs, but instead they have grabbed the first three records of the table for the last three records.

 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

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

After I hit refresh, those records then show what they should:

 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

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

This is problematic, as when a user adds a record then saves it, it shows a completely unrelated record. If they make a change on this apparition, it affects that record - this will easily lead to incorrectly updated/deleted records etc. Not good!

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

So here's my script to create an audit table and its associated triggers:

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

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

I'm quite stumped as to why this is happening. All I can think of is perhaps a timing issue, though currently the trigger runs after the action which I figure would be the preference.

我相信这是我的触发器，因为当我删除它们时，它工作正常.我的数据库也从来没有出现过这样的错误.

I'm confident that it's my triggers as when I delete them, it works fine. My database also has never shown errors like this.

我使用 SQL Server 2005 和 MS Access 2007 客户端的 ODBC 连接.

I'm using SQL Server 2005 with an ODBC connection to MS Access 2007 clients.

推荐答案

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

As Nikola has explained, the issue relates to the usage of @@identity. This takes the identity value of the last record entry, so when I do an insert, the trigger does an insert to a different table. This leads to a different value for @@identity, which is what MS Access seems to be incorrectly relying upon. To fix this, I needed to save the @@identity value before running the trigger, then reinstate it. As shown below for the Insert trigger:

 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% 准确的东西.

It's a shame that Access appears to be built this way, relying on something that's not 100% accurate.

最后，我只在插入触发器上实现了这一点，即使其他触发器使用插入操作.经过一些简短的测试后，这似乎没问题.我现在已经在我所有普通用户可以编辑的表上推出了这个脚本，所以我很快就会知道这是否导致了问题.

In the end, I only implemented this on the Insert trigger, even though the other triggers use an insert operation. This seemed to be ok after some brief testing. I've now rolled this script out on all my tables that can be edited by general users, so won't be long before I know whether or not this has caused issues.

这篇关于为什么我的 SQL Server 审计触发器搞乱了 OBDC 调用/从 Access 刷新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！