Nhibernate cache problem while using SQL Server triggers

Question

Hey!

I am using Nhibernate with Fluent, to persist a SQL Server 2008 express database in a business application.

I have a class named Receipt which contains a list with many objects named ReceiptItems. The user can create a receipt, add Receiptitems to it, and edit it as long as its not marked Finished. This part works well and saves to the db correctly. Now for the problem:

I also have a trigger on the sql table Receipt, that fires if the inserted.Finished is true. The trigger fetches new prices from the "supplier table", and updates the prices for all ReceiptItems, in the ReceiptItems table.

When calling session.SaveorUpdate(value) and then transaction.Commit()

the latter causes the exception: StaleObjectStateException Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) in ReceiptItems

Removing the trigger ofcourse fixes the problem, but i need it to update the prices. Is there any way for nhibernate to ignore the error, and refresh its cache after the trigger fires?

straightforward class example definitions with Fluent mappings:

 public class Receipt
    {

        public Receipt() { }
        /// <summary>Identificator/// </summary>
        public virtual int Id { get; private set; }
        /// <summary> if finished true, cant edit data/// </summary>
        public virtual Boolean Finished { get; set; }
        /// <summary>Items of this Receipt/// </summary>
        public virtual IList<ReceiptItems> Items{ get; set; }
    }

    /// <summary>Mapping for NHibernate Fluent/// </summary>
    public class ProdajaMap : ClassMap<Prodaja>
    {

        public ReceiptMap()
        {
            Table("Receipt");
            OptimisticLock.All();
            DynamicUpdate();
            Id(x => x.Id);
            Map(x => x.Finished);
            HasMany<ReceiptItems>(x => x.Items).AsBag().KeyColumn("Receipt_ID");
        }
    }

public class ReceiptItem
    {

        public ReceiptItem() { }

        public virtual int Id { get; private set; }
        /// <summary>Id of the Receipt/// </summary>
        public virtual int Receipt_ID{ get; set; }

        /// <summary>Supplier price/// </summary>
        public virtual decimal Price{ get; set; }
        /// <summary>Supplier discount/// </summary>
        public virtual decimal Discount { get; set; }
    }

    /// <summary>Mapping for NHibernate Fluent/// </summary>
    public class ReceiptItemMap : ClassMap<ReceiptItem>
    {
        public ReceiptItemMap()
        {
            Table("ReceiptItems");
            OptimisticLock.All();
            DynamicUpdate();
            Id(x => x.Id);
            Map(x => x.Receipt_ID).Column("Receipt_ID");
            Map(x => x.Price);
            Map(x => x.Discount );

        }
    }

Thank you very much !

UPDATE:

I've found a nhibernate property, which does exactly what i need, as the Price and Discount values have to be generated by the trigger:

5.6. Generated Properties Generated properties are properties which have their values generated by the database. Typically, Hibernate applications needed to refresh objects which contain any properties for which the database was generating values. Marking properties as generated, however, lets the application delegate this responsibility to Hibernate. Essentially, whenever Hibernate issues an SQL INSERT or UPDATE for an entity which has defined generated properties, it immediately issues a select afterwards to retrieve the generated values.

Properties marked as generated must additionally be non-insertable and non-updateable.

As im new at this.., does the last sentance mean, that i can't insert or update the values with nhibernate?

Answer 1

Use SET NOCOUNT ON in the trigger to suppress (xx rows affected) "dummy" result sets from trigger processing.

Example SO question: TooManyRowsAffectedException with encrypted triggers