DataTable identity column not set after DataAdapter.Update/Refresh on table with "instead of"-trigger (SqlServer 2005)

Question

Within our unit tests we use plain ADO.NET (DataTable, DataAdapter) for preparing the database resp. checking the results, while the tested components themselves run under NHibernate 2.1. .NET version is 3.5, SqlServer version is 2005.

The database tables have identity columns as primary keys. Some tables apply instead-of-insert/update triggers (this is due to backward compatibility, nothing I can change). The triggers generally work like this:

create trigger dbo.emp_insert
  on dbo.emp
  instead of insert
as
begin
  set nocount on
  insert into emp ...
  select @@identity
end

The insert statement issued by the ADO.NET DataAdapter (generated on-the-fly by a thin ADO.NET wrapper) tries to retrieve the identity value back into the DataRow:

exec sp_executesql N'
insert into emp (...) values (...);
select id, ... from emp where id = @@identity
'

But the DataRow's id-Column is still 0. When I remove the trigger temporarily, it works fine - the id-Column then holds the identity value set by the database.

NHibernate on the other hand uses this kind of insert statement:

exec sp_executesql N'
insert into emp (...) values (...);
select scope_identity()
'

This works, the NHibernate POCO has its id property correctly set right after flushing. Which seems a little bit counter-intuitive to me, as I expected the trigger to run in a different scope, hence @@identity should be a better fit than scope_identity().

So I thought no problem, I will apply scope_identity() instead of @@identity under ADO.NET as well. But this has no effect, the DataRow value is still not updated accordingly.

And now for the best part: When I copy and paste those two statements from SqlServer profiler into a Management Studio query (that is including "exec sp_executesql"), and run them there, the results seem to be inverse! There the ADO.NET version works, and the NHibernate version doesn't (select scope_identity() returns null). I tried several times to verify, but to no avail. Well, actually that was what I would have expected - @@identity to be OK, and scope_identity() to fail.

Of course invoking it in Management Studio just shows the resultset coming from the database, whatever happens inside NHibernate and ADO.NET is another topic. Also, several session properties defined by T-SQL SET are different in the two scenarios (Management Studio query vs. application at runtime)

This is a real puzzle to me. I would be happy about any insights on that. Thank you!

Answer 1

Found it. The identity value actually was transmitted into the DataTable, just not in the column I expected. Instead of using existing column "id", ADO.NET created a new column "Column1".

Reason is this line at the end of the instead-of trigger:

select @@identity 

Unfortunately, NHibernate seems to require "select @@identity" at the end of instead-of triggers (this was mentioned in a Hibernate forum posting, and I verified it again now - it is indeed necessary). But I can go along from here (adapting NHibernate dialect is one possibility)...