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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With