Hello,

I am having issues with updating objects with NHibernate. When I call SaveOrUpdate() method or Flush() on the session which has my persistent object loaded, it throws HibernateException with the message:
"SQL insert, update or delete failed (expected affected row count: 1, actual affected row count: 2). Possible causes: the row was modified or deleted by another user, or a trigger is reporting misleading row count."

I am using NHibernate 1.0.2 with ASP.Net 2.0 (C#) and SQL Server 2000. My NHibernate code is embedded in a DAL layer (dll) which is included in the ASP.Net application. I am using session per Request (http) model.

I looked at the sql (using Profiler) which is generated by NHibernate to update the object. The sql on inspection seems fine. When I manually execute the same sql UPDATE command in Query Analyzer, I am getting back 2 result set messages (i.e.
1 rows affected
1 rows affected).
The update sql is:

exec sp_executesql N'UPDATE VCompany SET PhoneNumber = @p0, City = @p1, ZipCode = @p2, DateUpdated = @p3, DateReviewed = @p4, CountryCode = @p5, FaxNumber = @p6, Address2 = @p7, CompanyName = @p8, StateCode = @p9, Address1 = @p10, Url = @p11, DateEntered = @p12 WHERE Id = @p13', N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 datetime,@p4 datetime,@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 datetime,@p13 int', @p0 = N'test', @p1 = N'test', @p2 = N'test', @p3 = NULL, @p4 = NULL, @p5 = NULL, @p6 = N'', @p7 = N'test', @p8 = N'test', @p9 = N'MA', @p10 = N'test', @p11 = N'test', @p12 = 'Oct 13 2006 2:54:10:000PM', @p13 = 639090

I am wondering if these multiple messages are causing NHibernate to assume that multiple records have been updated and throw an exception. The Id on this class is unique, so only a single record is expected to be updated in the database.

Any help or comments will be appreciated.
Thanks.

-Imad

Yes, that is the problem.
_why_ is it giving back two changes, though?
Most likely reason is a trigger.

You were right, there was an update trigger on the table which was also returning a row count. I updated the trigger to set nocount on, and now NHibernate updates fine.

Thanks for your help.