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

如何使用OUTPUT获取标识列的值 - SQL Server(How to get a value of identity column using OUTPUT - SQL Server)

我有一张桌子和一个扳机

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?

最满意答案

这个实际上有效。

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.