附加到 SQL 服务器上的链接表时，停止访问使用错误的身份

TL:DR;版本:

如果我将一条记录插入到具有在不同表中插入记录的触发器的链接表中，Access 将显示全局标识(该不同表的标识)而不是正确的主键，并用如果存在具有对应标识的记录，则具有对应标识的记录的值.

有什么办法可以阻止/解决这种行为?

MCVE:

我有下表:

CREATE TABLE MyTable([ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,[Col1] [nvarchar](255) NULL，[Col2] [nvarchar](255) NULL)

使用以下信息(在创建触发器之前)为该表设置种子

INSERT INTO [MyTable]([Col1],[Col2])价值观('Col1','Col2')去 10

以及记录更改的下表:

CREATE TABLE MyTable_Changes([ID] [int] 非空，[Col1] [nvarchar](255) NULL，[Col2] [nvarchar](255) NULL，[IDChange] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY)

该表附加了以下触发器:

在插入、更新后在 MyTable 上创建触发器 MyTableTrigger作为开始设置无计数；INSERT INTO MyTable_Changes(ID, Col1, Col2)SELECT * FROM 插入结尾

MyTable 是 Microsoft Access 中的链接表，使用以下 ODBC 连接字符串:

 ODBC;DRIVER=SQL Server;SERVER=myserver;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyDB;

我使用的是 Access 2010 和一个 .accdb 文件

问题:

我正在通过 GUI 插入记录.我在启用触发器之前插入了几条记录，MyTable 的身份种子为 100，但 MyTable_Changes 的身份种子为 10.

当我在MyTable中添加一条新记录，并设置Col1等于A"时，插入后，插入记录的ID列显示为11，Col2显示为ID为11的Col2的值.Col1显示一般.按 F5 后，记录显示就像我刚刚添加的一样.

我尝试过的:

我读过很多帖子(比如

注意，Col1 和 Col2 更新后都变成了 ID 1 对应的值.刷新后，我添加的记录(ID 11，Col1 a，Col2 Null)正确显示.

解决方案

一个 ODBC 跟踪显示 Access 确实在调用 SELECT @@IDENTITY(而不是 SCOPE_IDENTITY()>) 将行插入 SQL Server 链接表后:

Database1 e00-1490 EXIT SQLExecDirectW 返回代码 0 (SQL_SUCCESS)HSTMT 0x00000000004D6990WCHAR * 0x000000000F314F28 [ -3] "INSERT INTO "dbo"."Table1" ("txt") VALUES (?) 0"SDWORD -3...Database1 e00-1490 EXIT SQLExecDirectW 返回代码 0 (SQL_SUCCESS)HSTMT 0x00000000004D6990WCHAR * 0x000007FED7E6EE58 [ -3] "SELECT @@IDENTITY 0"SDWORD -3

此外，这种行为似乎取决于所使用的 ODBC 驱动程序，因为对 MySQL Connector/ODBC 的类似测试表明，在插入一行后，Access 不会调用相应的 MySQL 函数 LAST_INSERT_ID()进入 MySQL 链接表.

鉴于 Access 正在调用 SELECT @@IDENTITY，我们必须按如下方式修改触发器(来源:此处) 将@@IDENTITY 值重置回其原始值

在 mytable 上创建触发器 mytable_insert_trigger 以插入为声明@identity int声明@strsql varchar(128)设置@identity=@@identity--你的代码--插入第二个表...--你的代码设置@strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp'执行(@strsql)

TL:DR; version:

If I insert a record into a linked table that has a trigger that inserts a record in a different table, Access displays the global identity (the identity of that different table) instead of the correct primary key, and fills the columns with the values of the record with the corresponding identity if a record with the corresponding identity exists.

Is there any way to stop/work around this behaviour?

MCVE:

I have the following table:

CREATE TABLE MyTable(
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL
)

The table is seeded with the following information (before creating the trigger)

INSERT INTO [MyTable]
           ([Col1]
           ,[Col2])
     VALUES
           ('Col1'
           ,'Col2')
GO 10

And the following table that logs changes:

CREATE TABLE MyTable_Changes(
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL,
    [IDChange] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
)

This table has the following trigger attached to it:

CREATE TRIGGER MyTableTrigger ON MyTable AFTER Insert, Update
    AS
BEGIN 
    SET NOCOUNT ON;
    INSERT INTO MyTable_Changes(ID, Col1, Col2)
    SELECT * FROM Inserted
END

MyTable is a linked table in Microsoft Access, using the following ODBC connection string:

 ODBC;DRIVER=SQL Server;SERVER=myserver;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyDB;

I'm using Access 2010, and an .accdb file

The problem:

I'm inserting records through the GUI. I've inserted several records before enabling the trigger, and the identity seed for MyTable is 100, but for MyTable_Changes, the identity seed is 10.

When I add a new record to MyTable, and I set Col1 equal to "A", after inserting, the ID column of the inserted record displays as 11, and Col2 displays as the value of Col2 for ID 11. Col1 displays normally. After hitting F5, the record displays like I just added it.

What I've tried:

I've read numerous posts (like this one). I've tried a compact & repair, changing the seed on the table in Access (which doesn't work since it's a linked table), but haven't been able to solve it.

For now, I've included a work-around that can open linked tables as a datasheet form and requeries after updating, then navigates to the last record, but this is far from optimal, since it increases the time it takes to add records, and people can't use the navpane to open tables and add records.

Picture: (left: before adding the record, right: after)

Note that both Col1 and Col2 changed to the values corresponding with ID 1 after updating. After refreshing, the record I had added (ID 11, Col1 a, Col2 Null) properly showed.

解决方案

An ODBC trace reveals that Access is indeed calling SELECT @@IDENTITY (as opposed to SCOPE_IDENTITY()) after inserting the row into the SQL Server linked table:

Database1       e00-1490    EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00000000004D6990
        WCHAR *             0x000000000F314F28 [      -3] "INSERT INTO  "dbo"."Table1"  ("txt") VALUES (?) 0"
        SDWORD                    -3

...

Database1       e00-1490    EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00000000004D6990
        WCHAR *             0x000007FED7E6EE58 [      -3] "SELECT @@IDENTITY 0"
        SDWORD                    -3

Furthermore, this behaviour appears to depend on the ODBC driver being used, since a similar test with MySQL Connector/ODBC shows that Access does not call the corresponding MySQL function LAST_INSERT_ID() after inserting a row into a MySQL linked table.

Given that Access is calling SELECT @@IDENTITY, we must modify our trigger as follows (source: here) to reset the @@IDENTITY value back to its original value

create trigger mytable_insert_trigger on mytable for insert as

declare @identity int
declare @strsql varchar(128)

set @identity=@@identity
--your code
--insert into second table ...
--your code
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1) as id into #tmp'
execute (@strsql)