检索 MS SQL 2008 中生成的 ID

发布于 2025-01-08 08:49:35 字数 1071 浏览 0 评论 0原文

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

我计划使用这样的 INSERT 语句，

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

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

微软写道：

如果指定了 OUTPUT 子句而没有指定 INTO 关键字，DML操作的目标不能有任何启用为给定的 DML 操作定义的触发器。例如，如果 OUTPUT 子句定义在 UPDATE 语句中，目标表不能启用任何 UPDATE 触发器。 http://msdn.microsoft.com/en-us/library/ms177564.aspx

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

对于 INSERT 来说这是一个好方法吗？它之所以有效，是因为 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 块中编写多个子句时，我是否应该更好地使用 SELECT SCOPE_DENTITY() ？

谢谢，最好的，伯恩哈德

原文

I'm converting a ColdFusion Project from Oracle 11 to MS SQL 2008. I used SSMA to convert the DB including triggers, procedures and functions. Sequences were mapped to IDENTITY columns.

I planned on using INSERT-Statements like

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

This throws an error since the table has a trigger defined, that AFTER INSERT writes some of the INSERTED data to another table to keep a history of the data.

Microsoft writes:

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

I'm now wondering what is the best practice fo firstly retrieve the generated id and secondly to "backup" the INSERTED data in a second table.

Is this a good approach for the INSERT? It works because the INSERTED value is not simply returned but written INTO a temporary variable. It works in my tests as Microsoft describes without throwing an error regarding the trigger.

<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>

Should I use the OUTPUT clause at all? When I have to write multiple clauses in one cfquery-block, shouldn't I better use SELECT SCOPE_DENTITY() ?

Thanks and best,
Bernhard

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小镇女孩 2025-01-15 08:49:35

我认为这就是你想做的：

<cfquery name="qryInsert" datasource="db" RESULT="qryResults">
   INSERT INTO mytable (col1, col2)
</cfquery>

<cfset id = qryResults.IDENTITYCOL>

I think this is what you want to do:

<cfquery name="qryInsert" datasource="db" RESULT="qryResults">
   INSERT INTO mytable (col1, col2)
</cfquery>

<cfset id = qryResults.IDENTITYCOL>

回复收藏 0 原文

〃安静 2025-01-15 08:49:35

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

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< 中写入此内容/code>，你应该在输出中得到一行。我不精通 CF，所以我不确定它是否必须看到某种 select 才能知道它将返回结果集（但您可以在 Management Studio 中尝试以确认我不是在开玩笑）：

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

现在您也应该将插入后逻辑移至此触发器。

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

This seems to work - the row gets inserted, the instead of trigger returns the result, the after trigger doesn't interfere, and the after trigger logs to the table as expected:

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

Now, if you write this in your cfquery, you should get a row back in output. I'm not CF-savvy so I'm not sure if it has to see some kind of select to know that it will be returning a result set (but you can try it in Management Studio to confirm I am not pulling your leg):

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

Now you should just move your after insert logic to this trigger as well.

Be aware that right now you will get multiple rows back for (which is slightly different from the single result you would get from SCOPE_IDENTITY()). This is a good thing, I just wanted to point it out.

回复收藏 0 原文