如何使用 OUTPUT 获取标识列的值 - SQL Server

发布于 2024-11-08 17:55:16 字数 557 浏览 0 评论 0原文

我有一个表和一个触发器。

create table test(id int not null identity(1,1) primary key, data int);
create trigger insteadTestInsert ON test INSTEAD OF INSERT 
AS
BEGIN
  INSERT INTO test(data) select data FROM inserted;
END;

启用触发器后，以下查询

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output inserted.* into @tmp_table
values (10);
select * from @tmp_table;

返回 id = 0, int_val = 10 。
如果我禁用（或删除）触发器，查询将返回 id 的正确值。

如何使OUTPUT将正确的结果插入表变量中？

原文

I have a table and a trigger

create table test(id int not null identity(1,1) primary key, data int);
create trigger insteadTestInsert ON test INSTEAD OF INSERT 
AS
BEGIN
  INSERT INTO test(data) select data FROM inserted;
END;

When trigger is enabled, the following query

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output inserted.* into @tmp_table
values (10);
select * from @tmp_table;

returns id = 0, int_val = 10 .
If I disable(or drop) the trigger, the query returns the proper value of id.

How to make OUTPUT insert proper results into table variable?

分享到QQ

分享到微博

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

发布评论

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

赴月观长安 2024-11-15 17:55:16

来自 MSDN

从 OUTPUT 返回的列反映
INSERT 后的数据，
UPDATE 或 DELETE 语句有
已完成但在触发器之前
已执行。

所以问题是在执行触发器之前插入没有设置 id。

即使像这样删除触发器的内容，

create trigger insteadTestInsert on test instead of insert 
as
begin
    return;
end

您也会看到 inserted 仍然填充，尽管表中没有inserted 任何内容。基本上，output 语句中的 inserted 与触发器内部的 inserted 相匹配。

From MSDN

Columns returned from OUTPUT reflect
the data as it is after the INSERT,
UPDATE, or DELETE statement has
completed but before triggers are
executed.

So the problem is that before your trigger was executed inserted didn't have id set.

Even if remove content of the trigger like this

create trigger insteadTestInsert on test instead of insert 
as
begin
    return;
end

you will see that inserted is still populated although nothing was inserted into the table. Basically inserted in output statement matches inserted inside of the trigger.

回复收藏 0 原文

朕就是辣么酷 2024-11-15 17:55:16

这个确实有效。

declare @tmp_table table(seq int identity, id int, int_val int);
insert into test(data) 
output inserted.data into @tmp_table(int_val)
values (11),(12),(13);

update @tmp_table set id = seq + @@identity - @@rowcount
select * from @tmp_table;
select top 2 * from test order by id desc;

限制是表 test 上不能有任何其他会“损坏”@@identity 变量的触发器。

This one actually works.

declare @tmp_table table(seq int identity, id int, int_val int);
insert into test(data) 
output inserted.data into @tmp_table(int_val)
values (11),(12),(13);

update @tmp_table set id = seq + @@identity - @@rowcount
select * from @tmp_table;
select top 2 * from test order by id desc;

The restriction is that you must NOT have any other triggers on the table test that would "corrupt" the @@identity variable.

回复收藏 0 原文

强者自强 2024-11-15 17:55:16

这是一个 hack，但它有效：

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output @@IDENTITY + 1, inserted.data into @tmp_table
values (10);
select * from @tmp_table;

请参阅 http://msdn.microsoft.com /en-us/library/ms190315.aspx 了解有关使用 @@IDENTITY 的详细信息。

This is a hack, but it works:

declare @tmp_table table(id int, int_val int);
insert into test(data) 
output @@IDENTITY + 1, inserted.data into @tmp_table
values (10);
select * from @tmp_table;

See http://msdn.microsoft.com/en-us/library/ms190315.aspx for details on using @@IDENTITY.

回复收藏 0 原文

~没有更多了~