System Triggers for Replication and the NOCOUNT option

When setting up replication in Sql Server 2008 the system creates triggers on every replicated t开发者_如何学Cable in order to facilitate the transfer of data to the subscribing databases.

Would it be expected behaviour for system generated triggers to use SET NOCOUNT ON to prevent their actions affecting the value of the @@ROWCOUNT function?

Background

I am using NHibernate with a Sql Server 2008 backend database. The database has replication enabled which creates system generated triggers on the published tables to facilitate the data transfer to the subscribers.

Without the replication everything works fine, but with the replication NHibernate’s rows affected verification checks fail. The number of rows that ADO.Net says it has affected appears to include the rows affected by the associated trigger in addition to the rows affected by the command itself.

In the original question I mentioned the @@ROWCOUNT Sql Server function, but what I really mean is the “rows affected” value returned by the ADO.Net ExecuteNonQuery method. I suppose I was working on the assumption (possibly incorrectly) that that latter was fed by the former.

As a temporary solution we have modified the replication triggers to add the SET NOCOUNT ON before any updates and to reverse this after any updates are complete. This solves our problem for the time being, but is not a viable permanent solution. According to advice we have received and common sense, editing the system triggers is not recommended.

This does however suggest to me that we have identified the exact problem. The rows affected by the triggers are getting included in the final rows affected count for the current command. NHibernate expects only a known number of rows to be affected, it doesn’t provision for any unknown (as far as NHibernate is concerned) triggers adding to this count.

We are currently in the process of investigating options that involve extending NHibernate to add capability to deal with or at least suppress this. Our resources include this SO question.

I have also found this post that seems to suggest that system generated triggers would already set the NOCOUNT option to ON by default, negating the need to modify them. This is definitely not the case for us, so I was wondering why this might be.

1. What is the default situation with regard to the system generated triggers?
2. Is the behaviour configurable?
3. Does it depend on the type of replication – merge, transactional?

I have not been able to determine the answer to the original question - (should/can/how) Sql Server system generated replication triggers automatically include the SET NOCOUNT ON option.

Our DBA team suggest the answer is no. They also say (quite rightly) that they will not customise system triggers in production.

Options left open to me are...

1. Modify/extend NHibernate to not throw the TooManyRowsAffectedException when a row count mismatch is detected.
2. Rip out NHibernate and replace with stored procedures and manual mapping to business objects.

We have chosen option 1.

My solution was to modify the NHibernate (2.1) Core in the following way...

1. First I added a new configuration option to control my new behaviour.
2. Then I modified the signature of the VerifyOutcomeNonBatched and VerifyOutcomeBatched methods from the NHibernate.AdoNet.Expectations class to accept the new configuration value as a parameter. This involved modifying several places where these methods are called.
3. Finally I modified those two methods so they each throw/suppress the TooManyRowsAffectedException according to the config option.

I would be interested in any suggested alternative implementations.