#sql #sql-server #triggers #identity-column
#sql #sql-сервер #триггеры #идентификатор-столбец
Вопрос:
У меня хорошо известная проблема с триггером и @@IDENTITY . Я создал новую таблицу аудита и триггер для вставки в нее строк аудита. Мы используем программное обеспечение, которое использует @@IDENTITY , и это вызывает конфликт с идентификатором, сгенерированным, когда триггер вставляет новую строку. У меня нет доступа к используемому коду @@IDENTITY .
Мне нужны идеи о том, как я могу самостоятельно сгенерировать значение идентификатора. Я не могу использовать GUID, потому что для меня важен порядок. Если я заменю столбец Id столбцом datetime со значением по умолчанию GETDATE() , гарантирует ли это, что он будет уникальным?
Спасибо
Ответ №1:
GETDATE() не будет уникальным. Его точность такова, что несколько почти одновременных событий могут быть предоставлены в одно и то же время.
Если вы вынуждены генерировать собственные значения идентификаторов, чтобы не мешать @@IDENTITY, вы можете сделать следующее…
INSERT INTO
myTable (
id,
field1,
field2
)
SELECT
(SELECT ISNULL(MAX(id), 0) FROM myTable WITH(TABLOCKX)) 1,
@p1,
@p2
Это неявно связано с его собственной транзакцией и гарантирует уникальные значения.
Редактировать
Мой первоначальный комментарий должен был заключаться в том, что это НЕ будет работать при вставке нескольких записей, и вместо этого вам нужно будет перебирать исходные записи по отдельности, вставляя их по одному за раз.
Однако следующий пример может подойти вам для обработки наборов данных…
WITH
sorted_data AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY field1) AS set_id, -- DO NOT include a PARTITION here
*
FROM
inserted
)
INSERT INTO
myTable (
id,
field1,
field2
)
SELECT
(SELECT ISNULL(MAX(id), 0) FROM myTable WITH(TABLOCKX)) set_id,
@p1,
@p2
FROM
sorted_data
Это позволит генерировать уникальные идентификаторы для каждой строки и будет безопасно для параллельных процессов, использующих один и тот же код.
Редактировать
Я добавил WITH(TABLOCKX) , чтобы другие процессы не читали таблицу во время ее обновления. Это не позволяет параллельным процессам устанавливать один и тот же MAX (id), а затем пытаться вставить повторяющиеся идентификаторы в новые записи.
(Единая структура запроса уже предотвращала изменение записей после их чтения, но не препятствовала другим процессам, считывающим из таблицы «между» считываемым MAX (id) и всеми новыми вставляемыми записями.)
Комментарии:
1. Безопасен ли ваш параллельный код? Даже если триггер вставляет несколько строк?
2. ДА;
This is implicitly within it's own transaction and will guarantee unique values.3. Даже если триггер вставляет несколько строк?
4. Мне любопытно, потому что я не совсем уверен, как работает неявная транзакция, но что помешало бы генерировать перекрывающиеся наборы идентификаторов, если одновременно выполняются два оператора insert, поскольку в myTable нет блокировки при вычислении MAX (ID)?
5. Если значение
MAX(ID)было получено в одном операторе, а затем использовано в другом операторе, создается условие гонки, если не используются транзакции и блокировка таблицы / строки.MAX(ID)Однако, когда запрос получен в рамках подзапроса, весь запрос представляет собой один оператор и поэтому неявно уже находится в его собственной транзакции.
Ответ №2:
Я знаю, что вы, вероятно, не сможете ничего изменить, но проблема в том, что программное обеспечение использует @@IDENTITY, что не входит в область видимости. Вставки в ЛЮБУЮ таблицу изменят @@IDENTITY. Вместо этого программное обеспечение должно использовать функцию scope_identity() .