Why is my SQL Server auditing trigger messing up O

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.

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.