sql - @@IDENTITY 和触发问题

Question

我遇到了触发器和 @@IDENTITY 的众所周知的问题。我创建了一个新的审计表和一个用于在其上插入审计行的触发器。我们使用的软件正在使用 @@IDENTITY，这会导致与触发器插入新行时生成的 Id 发生冲突。我无法访问使用 @@IDENTITY 的代码。

我需要关于如何通过自己产生身份值(value)的想法。我不能使用 GUID，因为顺序对我很重要。如果我将 Id 列替换为具有默认值 GETDATE() 的日期时间列，它是否保证它是唯一的？

谢谢

Answer 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

这将为每一行生成唯一的 ID，并且对于使用相同代码的并发进程是安全的。

编辑

我添加了 WITH(TABLOCKX) 以防止其他进程在表更新时读取表。这可以防止并发进程建立相同的 MAX(id)，然后尝试在新记录中插入重复的 id。

(单一查询结构已经阻止了记录在读取后被更改，但没有阻止其他进程从正在读取的 MAX(id) 和插入的所有新记录之间的表中读取数据。)