sql-server-2008 - 在 MS SQL 2008 中检索生成的 ID

Question

我正在将 ColdFusion 项目从 Oracle 11 转换为 MS SQL 2008。我使用 SSMA 来转换数据库，包括触发器、过程和函数。序列被映射到 IDENTITY 列。

我计划使用 INSERT-Statements 之类的

INSERT INTO mytable (col1, col2)
OUTPUT INSERTED.my_id
values('val1', 'val2')

这会引发错误，因为该表定义了触发器，即 AFTER INSERT 将一些 INSERTED 数据写入另一个表以保留数据的历史记录。

微软写道:

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers. http://msdn.microsoft.com/en-us/library/ms177564.aspx

我现在想知道首先检索生成的 id 其次在第二个表中“备份”插入数据的最佳实践是什么。

这是插入的好方法吗？它起作用是因为 INSERTED 值不是简单地返回而是写入到临时变量中。它在我的测试中工作，正如微软所描述的那样，不会引发关于触发器的错误。

<cfquery>
DECLARE @tab table(id int);
INSERT INTO mytable (col1, col2)
OUTPUT INSERTED.my_id INTO @tab
values('val1', 'val2');
SELECT id FROM @tab;
</cfquery>

我应该使用 OUTPUT 子句吗？当我必须在一个 cfquery-block 中编写多个子句时，我不应该更好地使用 SELECT SCOPE_DENTITY() 吗？

谢谢，最好，
伯恩哈德

Answer 1

这似乎有效 - 行被插入，而不是触发器返回结果，后触发器不会干扰，后触发器按预期记录到表中:

CREATE TABLE dbo.x1(ID INT IDENTITY(1,1), x SYSNAME);

CREATE TABLE dbo.log_after(ID INT, x SYSNAME, 
  dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
GO

CREATE TRIGGER dbo.x1_after
ON dbo.x1
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.log_after(x) SELECT x FROM inserted;
END
GO

CREATE TRIGGER dbo.x1_before
ON dbo.x1
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tab TABLE(id INT);

    INSERT dbo.x1(x)
        OUTPUT inserted.ID INTO @tab
        SELECT x FROM inserted;

    SELECT id FROM @tab;
END
GO

现在，如果你在 cfquery 中写下这个，您应该在输出中返回一行。我不精通 CF，所以我不确定它是否必须看到某种 select知道它会返回一个结果集(但你可以在 Management Studio 中尝试以确认我没有拉你的腿):

INSERT dbo.x1(x) SELECT N'foo';

现在，您也应该将插入后的逻辑也移动到此触发器。

请注意，现在您将返回多行(这与您从 SCOPE_IDENTITY() 获得的单个结果略有不同)。这是一件好事，我只是想指出这一点。