gpt4 book ai didi

c# - 无法获取插入行的标识。触发器返回结果集并且服务器选项 'disallow results from triggers' 为真

转载 作者:太空宇宙 更新时间:2023-11-03 22:46:57 25 4
gpt4 key购买 nike

我有一个 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_table 插入语句的。

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

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

现在触发器引发错误:

A trigger returned a resultset and the server option 'disallow results from triggers' is true.

我无权修改服务器变量。我只想将触发器插入到日志中,然后将插入的 ID 返回给 C#。我该怎么做?

最佳答案

@@identity 已知存在缺陷,因为它会根据您 session 中执行的最后一次插入提供答案。

如果切换到 scope_identity ,正如大多数人已经建议的那样,它将解决问题而无需更改触发器 - 因为触发器定义了自己的范围。

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. [...] SCOPE_IDENTITY() returns the IDENTITY value inserted in T1.


另外,请注意您当前的触发器已损坏 - 它假设 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

关于c# - 无法获取插入行的标识。触发器返回结果集并且服务器选项 'disallow results from triggers' 为真,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49405480/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com