How do I tell NHibernate that a trigger is updating another table?

Question

I got a TooManyRowsAffectedException exception while working with NHibernate, and I saw workarounds for this by introducing a different dispenser, for example, here TooManyRowsAffectedException with encrypted triggers , or by changing triggers in the database to use SET NOCOUNT ON (I cannot use this, since I don’t want to modify the database - it is very complex, more than a hundred tables are connected with each other, and I do not want to mess with it, as other applications use it). I do not understand why this exception occurs. All I do is that I have a Sample object that received a pair of values ​​that I check, and if the values ​​meet the given criteria, I set the Sample.IsDone string to "Y" (in our database, all the logical elements represented by char Y or N). The code is very simple:

IQueryable<Sample> samples = session.Query<Sample>().Where(s => s.Value == desiredValue); foreach (Sample sample in samples) { sample.IsDone = 'Y'; session.Flush(); // Throws TooManyRowsAffectedException } session.Flush(); // Throws TooManyRowsAffectedException 

Call Flush whether I call it inside the loop or outside. Is there something I'm doing wrong or is it only related to how the database is created? I tried calling SaveOrUpdate () in the sample before Flush (), but didn't change anything. I know I can get around this exception, but I would rather understand the source of the problem.

Note. In the exception, he tells me that the actual number of rows is 2, and the expected one is 1. Why does it update 2 rows, since I only change one row?

Thank you all for your help!

EDIT:

I managed to find out that the reason for this is that the row in the container table is updated in the database (the containers contain samples) when the sample is updated. Is there a way to configure NHibernate so that it knows about this trigger and expects the correct number of rows to be updated?

Answer 1

The only work I found is shown in the code snippet below using Fluent NHibernate. This is ugly as it is hard SQL coding in your mapping, but it really works. The Check property is set to None, so the counters are ignored. I don't know if you can accomplish this using direct HBM files, but there is probably a way. It would be great if NHibernate (or Fluent NH) had a configuration option to either set the expected updated row counter or ignore it if necessary.

 public class OrderMap : ClassMap<Order> { public OrderMap() { Id(c => c.Id, "order_id").GeneratedBy.Native(); Table("order"); Map(c => c.GroupId, "group_id"); Map(c => c.Status, "status"); Map(c => c.LocationNumber, "location"); SqlInsert("insert into order (group_id, status, location) values (?, ?, ?)").Check.None(); SqlUpdate("update order set group_id = ?, status = ?, location = ? where order_id = ?")).Check.None(); SqlDelete("delete order where order_id = ?").Check.None(); } } 

EDIT: For those unfortunate people who don't know about Fluent NHibernate or love the painful generation of HBM files manually, here is the HBM file for this sample:

 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="none" default-lazy="true"> <class xmlns="urn:nhibernate-mapping-2.2" mutable="true" name="Your.DomainModel.Entities.Order, Your.DomainModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="order"> <id name="Id" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" unsaved-value="0"> <column name="order_id" /> <generator class="identity" /> </id> <property name="GroupId" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <column name="group_id" /> </property> <property name="Status" type="System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <column name="status" /> </property> <property name="LocationNumber" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> <column name="loc_num" /> </property> <sql-insert check="none">insert into order (group_id, status, location) values (?, ?, ?)</sql-insert> <sql-update check="none">update order set group_id = ?, status = ?, location = ? where order_id = ?</sql-update> <sql-delete check="none">delete order where order_id = ?</sql-delete> </class> </hibernate-mapping> 

Answer 2

Check with sql profiler that is running. anjlab sql profiler always did the trick for me.

After that, check if you have triggers in this table - maybe they cause some problems.

EDIT

You have to change your trigger like this: http://www.codewrecks.com/blog/index.php/2009/03/25/nhibernate-and-toomanyrowsaffectedexception/

Answer 3

You can do nothing to tell NHibernate that the trigger has updated another object in the database, except to ignore it, as Sixto Saez shows in his answer. However, you can use EventListeners to execute the trigger action in the code. Or set SET NOCOUNT ON at the start of the trigger and SET NOCOUNT OFF at the end if the update is not relevant to the rest of your transaction.