Should <subclass> honor <sql-insert>?

jzimmerman · **Joined:** Tue Apr 10, 2007 11:12 am **Posts:** 2

Using NHibernate 1.2.0CR1 I am trying to use the table per class hierarchy inheritance mapping strategy. Because of our database structure I need to use a view to pull all of the necessary pieces together, which is why I placed the view name in for the table name.
I am able to retrieve data from the database but when I attempt to save new PropertyValue instances I get the following SQL exception:

Code:

View or function 'VW_PROPERTYVALUE' is not updatable because the modification affects multiple base tables.

I've tried moving the <sql-insert> out of the subclass mapping and into the base class mapping with no effect. Should the <sql-insert> be honored by the <class> or <subclass> elements? Why is NHibernate trying to insert into the view?

Here is my mapping for this particular class:

Code:

  <class name="PropertyValue" table="VW_PROPERTYVALUE">
    <id name="Id" column="PROPERTYVALUE_PK" type="long" unsaved-value="-1">
      <generator class="native">
        <param name="sequence">SEQ_PK</param>
      </generator>
    </id>
    <discriminator column="CLRTYPE" type="String" />

    <many-to-one name="PropertyDefinition" class="PropertyDefinition" column="PROPERTYDEF_FK" lazy="false" />

    <any name="Entity" meta-type="long" id-type="long">
      <meta-value class="User" value="1" />
      <column name="ENTITYTYPE_FK" />
      <column name="ENTITY_FK" />
    </any>

    <subclass name="IntegerPropertyValue" extends="PropertyValue" discriminator-value="System.Int32">
      <property name="Data" column="IntValue" type="int" />
      <sql-insert>insert into TBL_PROPERTYVALUE (PROPERTYDEF_FK, ENTITY_FK, IntValue) VALUES (?, ?, ?)</sql-insert>
    </subclass>
    <subclass name="DateTimePropertyValue" discriminator-value="System.DateTime">
      <property name="Data" column="DateTimeValue" type="DateTime" />
    </subclass>
    <subclass name="StringPropertyValue" discriminator-value="System.String">
      <property name="Data" column="StringValue" type="string" />
    </subclass>
  </class>

Can anyone help?

pitchum · **Joined:** Sat Sep 03, 2005 4:37 pm **Posts:** 2

I'm afraid your problem has nothing to do with hibernate.
It seems that you just cannot perform update/insert queries on your view.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Not sure what DB you're using, but SQL Server does not allow an update to a view where the view spans multiple tables if the updated fields in the update statement includes fields that are from more than one underlying table.

HTH,

Symon.

paulh · **Posted:** Tue May 08, 2007 7:50 pm

One trick you could pull here is to use an INSTEAD OF trigger on the view to partition the various bits into the appropriate tables.

I'm doing this on a current project as a way of generating a complete version history of an entity and it works fine.

jzimmerman · **Joined:** Tue Apr 10, 2007 11:12 am **Posts:** 2

I am fully aware that you can't insert back into a view that spans multiple tables... I'm not that much of a newbie at this stuff.

I have since tabled the portion of my project that my original post related to. The schema design was bad to start with, making the use of NHibernate (or any other ORM) with this particular product feature difficult if not impossible.

I know that I cannot insert back into a view that spans multiple tables. What I am curious to know is why NHibernate doesn't recognize the <sql-insert> and <sql-update> elements in my class and sub-class mappings. No matter what I did with these elements NHibernate would always generate its own SQL for inserts and updates. Is there really no way to override NHibernate's default behavior in this situation?