Cant get identity of inserted row. A trigger returned a resultset and the server option 'disallow results from triggers' is true

Question

I have a C# program that inserts into a table that has an identity column (id).

C# code

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 Trigger

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

The code works fine, but the trigger causes the @@identity to be that of the log_table insert statement.

So I added this line at the bottom of the trigger:

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

Now the trigger raises an error:

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

I don't have access to modify server variables. All I want is for the trigger to insert into a log, then the inserted id be returned to c#.. How can I do that?

Answer 1

@@identity is known to be flawed because it provides an answer based on the last insert performed within your session.

If you switch to scope_identity, as most people would already recommend, it will resolve the issue without having to change the trigger - because the trigger defines its own scope.

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.

---

Also, please note that your current trigger is broken - it assumes inserted contains exactly 1 row, when in fact it may contain 0, 1 or multiple rows. You should instead have something like:

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