sql - 在附加到 SQL Server 上的链接表时阻止 Access 使用错误的标识

Question

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])
     VALUES
           ('Col1'
           ,'Col2')
GO 10

以及记录更改的下表:

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
)

此表附加了以下触发器:

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 是 Microsoft Access 中的链接表，使用以下 ODBC 连接字符串:

 ODBC;DRIVER=SQL Server;SERVER=my\server;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 后，记录显示为我刚刚添加的记录。

我尝试过的:

我读过很多帖子(比如 this one )。我已经尝试过压缩和修复，更改 Access 中表上的种子(这不起作用，因为它是一个链接表)，但一直无法解决。

目前，我已经包含了一个解决方法，可以将链接表作为数据表形式打开并在更新后重新查询，然后导航到最后一条记录，但这远非最佳，因为它增加了查询所需的时间添加记录，人们不能使用导航 Pane 打开表格和添加记录。

图片:(左:添加记录前，右:添加记录后)

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

Answer 1

ODBC 跟踪显示 Access 在将行插入 SQL Server 链接表后确实调用了 SELECT @@IDENTITY(与 SCOPE_IDENTITY() 相对):

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

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

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

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)