追加到SQL Server上的链接表时，阻止Access使用错误的身份 [英] Stop Access from using wrong identity when appending to linked table on SQL server

问题描述

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值.一般.按下F5后，记录显示为我刚刚添加的记录.

我尝试过的事情:

我已经阅读了许多帖子(例如此帖子).我已经尝试过紧凑的&进行修复，更改Access中表的种子(由于它是链接表而无法使用)，但无法解决.

目前，我提供了一种变通方法，该方法可以将链接表作为数据表形式打开并在更新后重新查询，然后导航到最后一条记录，但这并不是最佳选择，因为这增加了花费时间.添加记录，人们将无法使用导航面板打开表并添加记录.

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

请注意，更新后Col1和Col2均更改为与ID 1对应的值.刷新后，我添加的记录(ID 11，Col1 a，Col2 Null)正确显示.

解决方案

ODBC跟踪显示，在将行插入SQL Server链接表后，Access确实在调用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进行的类似测试表明，在将一行插入MySQL链接表之后，Access不会调用相应的MySQL函数LAST_INSERT_ID(). /p>

鉴于Access正在调用SELECT @@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)

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=my\server;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)

这篇关于追加到SQL Server上的链接表时，阻止Access使用错误的身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！