Why is my SQL Server auditing trigger messing up OBDC call/refresh from Access?

Question

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.

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.

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

Anachronism

I believe that problem originates in insert trigger because insert changes the value of @@identity (SessionID is identity column). Workaround is to store @@identity in local variable: set @id = @@identity at the beginning of the trigger body (after set nocount on) and restore it at the end: SELECT Identity (Int, ' + Cast(@id As Varchar(10)) + ',1) AS id INTO #Tmp. – Lashio

@NikolaMarkovinović Thanks for the response. I've done declare @id int and set @id = @@identity after set nocount on and written SELECT Identity (Int, ' + Cast(@id As Varchar(10)) + ',1) AS id INTO #Tmp before the END of that block, but it's giving me an error: Incorrect syntax near ' + Cast(@id As Varchar(10)) + '. Can't say I quite understand this.. still trying to get a grip on T-SQL. – Anachronism

is the data actually wrong in the table or is Access just displaying them in an unexpected order? – Echikson

@SebastianMeine The data is actually wrong, briefly. I've tested this by editing the incorrect records that show up, and the changes save to those incorrect records. When I refresh, it shows the correct records. – Anachronism

I can't see why Identity (Int, @id, 1) throws a syntax error. After all, I have declared @id as an int. – Anachronism

how is the data displayed. Are you accessing the table directly or is there a query or even a view involved? – Echikson

@SebastianMeine I've tried both the direct table via access, and a multiple item form. Same outcome. – Anachronism

Ok it looks like @NikolaMarkovinović basically had it. I didn't get why he had the ' + .... + ', this makes no sense for an int input. It seems he left out the fact that the whole SELECT line is a t-sql string to EXEC. This is necessary as the Identity function can't take a variable input - see here: support.microsoft.com/kb/271827. I'll give Nikola a chance to come back and write up an answer, otherwise I will. – Anachronism

I'm terribly sorry for the confusion. Yes, the string needs to be executed via 'exec'. And please feel free to post the answer as you did valuable research. – Lashio

Answer 1

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

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.

Anachronism

+1. The problem is, MS Access reads @@identity to find the ID of newly inserted autonumber record. If the trigger does the insert into another table with an identity field it changes the value of @@identity and other scope dependant identity values. So Access ends up with wrong ID and rereads wrong record. – Lashio

@NikolaMarkovinović So that's Access' fault, not ODBC? – Anachronism

Exactly. I couldn't find an article that would prove that, but check scope_identity() - if they were using this function there would be no problem to start with. – Lashio