Остановите доступ от использования неправильной идентичности при добавлении в связанную таблицу на сервере SQL

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

Эта проблема:

Я вставляю записи через графический интерфейс. Я включил несколько записей перед включением триггера, и начальное число идентификаторов для MyTable равно 100, но для MyTable_Changes начальное число идентификаторов равно 10.

Когда я добавляю новую запись в MyTable и устанавливаю Col1 равным "A", после вставки столбец идентификатора вставленной записи отображается как 11, а Col2 отображается как значение Col2 для идентификатора 11. Col1 отображается нормально. После нажатия F5 запись отображается так, как будто я только что добавил ее.

Что я пробовал:

Я прочитал множество постов (как этот). Я попытался выполнить сжатие и восстановление, меняя начальное значение на таблице в Access (которая не работает, поскольку это связанная таблица), но не смог ее решить.

На данный момент я включил обходной путь, который может открывать связанные таблицы в виде таблицы данных и запросов после обновления, а затем переходить к последней записи, но это далеко не оптимально, поскольку увеличивает время, необходимое для добавления записей, и люди не могут использовать панель навигации для открытия таблиц и добавления записей.

Картинка: (слева: до добавления записи, справа: после)

Обратите внимание, что и Col1, и Col2 изменились на значения, соответствующие идентификатору 1 после обновления. После обновления, запись, которую я добавил (ID 11, Col1 a, Col2 Null), правильно показала.

Answer 1

Трассировка ODBC показывает, что Access действительно вызывает SELECT @@IDENTITY (в отличие от SCOPE_IDENTITY()) после вставки строки в связанную таблицу SQL Server:

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мы должны изменить наш триггер следующим образом (источник: здесь), чтобы сбросить значение @@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)

Answer 2

В конце концов, я пробовал многочисленные обходные пути для решения этой проблемы. Для тех, кто сталкивается с этим в будущем, вот некоторые из работающих, и мои соображения.

Сначала я просто переместил ввод данных в форму и добавил следующий код:

Private Sub Form_AfterInsert()
    Me.Requery
End Sub

Во время работы у этого были многочисленные недостатки.

Затем я просто увеличил значение идентификатора моей таблицы _Changes до уровня, превышающего обычный размер таблицы:

DBCC CHECKIDENT ('MyTable_Changes', RESEED, 10000);

Это позволяет избежать @@IDENTITY существующий в MyTableпоэтому неправильные данные больше не будут отображаться после добавления строки. Это работает, но Access больше не будет получать значения по умолчанию для пустых столбцов после добавления. Однако для других это может быть неактуально и может быть самым простым решением.

Я также попытался изменить столбец идентификатора на GUID

CREATE TABLE MyTable_Changes(
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL,
    [IDChange]  uniqueidentifier ROWGUIDCOL PRIMARY KEY NOT NULL
                CONSTRAINT [DF_MyTable_Changes_IDChange] DEFAULT newsequentialid()
)

Это сработало (с @@IDENTITY больше не менялся), выборка по умолчанию работала, это добавляло некоторую сложность (я вставлял в две таблицы, соединенные отношением один-много и должен был использовать OUTPUT пункт для получения идентификатора на одном из них), и мой босс решил, что GUID были не интуитивно понятны и не должны использоваться.

В конце концов, Горд Томпсон ответил тем же, что и я. Я изменил код, чтобы использовать переменную таблицы вместо временной таблицы, чтобы сделать область таблицы более явной.

CREATE TRIGGER MyTableTrigger ON MyTable AFTER Insert, Update
    AS
BEGIN 
    SET NOCOUNT ON;
    -- Capture @@identity
    DECLARE @identity int;
    SET @identity=@@identity;
    -- Inserts here
    INSERT INTO MyTable_Changes(ID, Col1, Col2)
    SELECT * FROM Inserted;

    -- reset @@identity
    DECLARE @strsql varchar(255)
    SET @strsql='
        DECLARE @t Table(id INTEGER IDENTITY(' + cast(@identity as varchar(10)) + ',1) PRIMARY KEY);
        INSERT INTO @t DEFAULT VALUES;
        '
    EXEC(@strsql);
END

Answer 3

Вы должны делать что-то еще в этой форме.

Я бы предложил вам удалить и заново создать связанную таблицу из Access. Я не могу воссоздать твой эффект.

Помните, что Access не отображает и не будет отображать идентификатор автоматического номера, пока вы не перейдете к новой записи или не нажмете Ctrl-s, чтобы сохранить текущую запись. Однако на самом деле нет причин беспокоиться или беспокоиться об этом "отсутствии" идентификатора PK в форме доступа до тех пор, пока не произойдет сохранение.

Из того, что я вижу, вы связались с неверной таблицей из Access. Поэтому я очень рекомендую вам удалить связанную таблицу в Access, перепроверить.

И в качестве быстрого теста, после того, как вы свяжете таблицу, переключите ее в режим разработки, чтобы убедиться, что Access увидит PK (access не увидит PK, если это, например, представление - но вы "можете" выбрать PK при связывании с вид через графический интерфейс).

редактировать - я запускаю ваши сценарии - играл немного. Я установил для первой таблицы значение auto inc at 1000. Снимок экрана после ввода трех строк выглядит следующим образом:

Из того, что я вижу, это правильно.

edit # 2: Из быстрого поиска в интернете - мы видим, что вы выпускаете, существует, но я в недоумении, почему мой пример работает на 100% просто отлично.