THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Michael Zilberstein

NHibernate and triggers

Recently had an interesting experience with NHibernate (I was against working with it from the beginning for many reasons, but unfortunately I wasn't the one to decide). I added trigger to a table that performs several update / insert operation to other tables. Suddenly updating table via NHibernate returned error:

NHibernate.AdoNet.TooManyRowsAffectedException: Unexpected row count: 6; expected: 1

So NHibernate doesn't support triggers that perform DML operations? Not quite. The solution is: add SET NOCOUNT ON before trigger performs DML operations. Looks like NHibernate doesn't really count DML operations, but messages of the type "2 rows updated" returned by SQL Server. And NOCOUNT setting suppresses these messages.

Published Wednesday, November 19, 2008 12:24 PM by Michael Zilberstein

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Probably a good idea in all triggers, whether you use NHibernate or not, unless you have a really good reason NOT to suppress the rowcount from each statement.  I add this directive to all triggers and stored procedures as a simple matter of course, and have done so for several years.

November 20, 2008 8:40 PM
 

Gauthier Segay said:

As far as I know, it does the same as checking ado.net's ExecuteNonQuery return value.

I think checking this value is a good practice in plain old ado.net as well while performing any side effect operation.

April 26, 2009 5:54 AM
 

Andriy Buday said:

Do you know how this could be solved within NHibernate?

December 24, 2009 9:42 AM
 

Michael Zilberstein said:

Andriy,

Don't know whether it is possible. TSQL solution I described (NOCOUNT ON) was enough on my system. If you have 3rd party system where you can't intervene in the db code - I guess, you have a problem in such a case.

December 24, 2009 9:49 AM
 

Andriy Buday said:

Michael, thank you for the answer.

Actually we can change all triggers, but I'm thinking about the impact it could produce, since we have almost 1000 tables in our system and subsystem we are creating with NHibernate works with near 10% of those tables. But anyway it looks doable.

Thanks.

December 24, 2009 9:59 AM
 

Michael Zilberstein said:

Andriy,

You don't have to change all the triggers but only those that perform DML (Insert/Update/Delete) operations.

December 24, 2009 10:09 AM
 

muralee said:

NOCOUNT ON will solve the problem,

In case if you are using FROM clause in update mention the source table with NOLOCK

January 7, 2010 4:42 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement