无法获取插入行的标识。触发器返回结果集，服务器选项'disallow results from triggers'为 true

Question

我有一个 C# 程序，它插入到具有标识列 (id) 的表中。

C# 代码

String sql = "insert into my_table (col_a,col_b) values (@val_a,@val_b); select @@identity;";
SqlCommand cmd = new SqlCommand(sql,conn);
... 
... // parameters code
...
Int inserted_id =  cmd.ExecuteScalar();   // Exception!

SQL 触发器

CREATE TRIGGER [dbo].[trg_my_trigger] ON [dbo].[my_table]
WITH EXEC AS CALLER
AFTER INSERT
AS
begin
declare @row_id int;
select @row_id = id from inserted;
insert into log_table(remarks1,remarks2) values ('new row inserted',@row_id);

end
GO

代码工作正常，但触发器导致@@identity是log_tableinsert 语句的。

所以我在触发器的底部添加了这一行：

begin
declare @row_id int;
select @row_id = id from inserted;
insert into log_table(remarks1,remarks2) values ('new row inserted',@row_id);
select @row_id;   -- this is causing the error
end
GO

现在触发器引发错误：

触发器返回结果集，服务器选项"禁止触发器的结果"为 true。

我无权修改服务器变量。 我想要的只是将触发器插入到日志中，然后将插入的 id 返回到 c#.。我该怎么做？

Answer 1

已知@@identity是有缺陷的，因为它根据会话中执行的最后一次插入提供了答案。

如果您切换到scope_identity，正如大多数人已经建议的那样，它将在不更改触发器的情况下解决问题 - 因为触发器定义了自己的范围。

例如，有两个表，T1

和 T2，并在 T1 上定义了一个 INSERT 触发器。将行插入到 T1 时，触发器将触发并在 T2 中插入一行。此方案演示了两个作用域：T1 上的插入和触发器在 T2 上的插入。

假设 T1 和 T2 都有标识列，则 @@IDENTITY 和 SCOPE_IDENTITY 在 T1 上的 INSERT 语句末尾返回不同的值。 @@IDENTITY返回在当前会话中跨任何范围插入的最后一个标识列值。[...] SCOPE_IDENTITY() 返回插入到 T1 中的 IDENTITY 值。

---

另外，请注意，您当前的触发器已损坏 - 它假设inserted正好包含 1 行，而实际上它可能包含 0、1 或多行。相反，您应该有类似的东西：

CREATE TRIGGER [dbo].[trg_my_trigger] ON [dbo].[my_table]
WITH EXEC AS CALLER
AFTER INSERT
AS
begin
insert into log_table(remarks1,remarks2)
select 'new row inserted',id
from inserted
end
GO