为什么我的SQL Server审核触发器搞乱了Access中的OBDC调用/刷新? [英] 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与ODBC连接到MS Access 2007客户端.

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

推荐答案

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

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审核触发器搞乱了Access中的OBDC调用/刷新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！