How to tell NHibernate that a trigger updates another table?

Question

I just got a TooManyRowsAffectedException while working with NHibernate and I've seen workarounds for that by injecting a different batcher like here TooManyRowsAffectedException with encrypted triggers, or by modifying the triggers in the database to use SET NOCOUNT ON (I can't use this one since I don't want to modify the database -- it is very complex with more than a hundred tables all related together and I don't want to go mess with it since other applications use it). What I don't understand is why this exception happens. All I do is that I have a Sample object that's got a couple of values that I check, and if the values fit with given criteria, I set the Sample.IsDone row to 'Y' (in our database all booleans are represented by a 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

The Flush call throws whether I put it inside the loop or outside. Is there something I am doing wrong or is it only related with the way the database is made? I tried calling SaveOrUpdate() on the sample before Flush(), but it didn't change anything. I know I can work around this exception, but I'd prefer understanding the source of the problem.

Note: In the exception it tells me the actual row count is 2 and the expected is 1. Why does it update 2 rows since I only change 1 row?

Thanks all for your help!

EDIT:

I was able to find out that the cause of that is because there is a trigger in the database updating a row in the Container table (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 right number of rows to get updated?

Answer 1

The only work around I've found is shown in the code snippet below using Fluent NHibernate. It is ugly since it is hard coding SQL into your mapping but it does work. The Check property is set to None so that the counts are ignored. I don't know if you can accomplish this using straight HBM files but there probably is a way. It would be great is there was a configuration option in NHibernate (or Fluent NH) to either set the expected updated row count or ignore it when needed.

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 folks that aren't aware of Fluent NHibernate or love painful generation of HBM files by hand, here is the HBM file for this sample mapping:

<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 a profiler the sql that gets executed. anjlab sql profiler always did the trick for me.

After that check to see if you have triggers on that table - maybe they are causing some trouble.

EDIT

You should alter your trigger like here : http://www.codewrecks.com/blog/index.php/2009/03/25/nhibernate-and-toomanyrowsaffectedexception/

Answer 3

There is nothing you can do to tell NHibernate that a trigger updated an other entity in the database except ignore it like Sixto Saez shows in his answer. However you can use EventListeners to do the trigger action in code. Or set the SET NOCOUNT ON at the beginning of the trigger and a SET NOCOUNT OFF at the end if the update does not matter for the rest of your transaction.