I have two tables in Oracle that link via a one-to-many relationship.

Both tables have their Primary Keys generated by an Oracle Trigger (before insert or update) that reads from a sequence. This is fine for the Primary Key part.

However, when linking the child table to the parent table (PK of Parent table = FK field on child table) - my session.saveorupdate() method fails.

I have tracked this back by disabling the triggers on the tables and disabling the database constraint - to identify the following:

Assuming that the sequence on both tables is reset and the first value of each sequence is 1 - when the saveorupdate method is called - the parent table seems to get an ID / PK of 2 but the FK field of the child table gets set as 1. The exception occurs due to the Oracle Constraint between the FK and PK fields.

This would therefore imply that the sequence of events within the SaveorUpdate seems to increment the sequence of the parent table (without inserting), insert the child record with the correct FK value before finally attempting to insert the Parent row (which takes the next value of the sequence).

Does anyone know how I can resolve the issue - it is unlikely that changes can be made re the triggers / sequences as these are used widely across the databases. Therefore, I am looking for a solution within the mapping files or by differing methods for save?

Any help greatly appreciated.....

Seems that at moment first the NHibernate creates new Id from sequence, and then the database overwrites it in the trigger.

So, what id generator class You are using? You probably must create a new one, as none of the ones listed in documentation seems to fit. Take the "identity" generator as base, as it uses database-created ID value fetching.

Gert

Thanks for the quick response.....

an extract of the mapping is as follows:

<class name="Wilson.NHibernate.Example.Contact, WilsonNHibernateExample" table="Contacts" discriminator-value="?" >
<id name="Id" column="ContactId" access="nosetter.camelcase" unsaved-value="0">
<generator class="native">
<param name ="sequence">seq_TBLCONTACTS</param>
</generator>
</id>
<discriminator column="ContactType" />

I cannot use identity as this does not seem to be supported by Oracle?

Yes, but there is no generator that seems to fit Your situation - means You need to write Your own. And You probably need to tweak the Oracke dialect also...

Or create an Jira entry... Or search the web, maybe someone has created a one...

Gert

Thanks for response.......

I found a previous post with something similar and it would appear that the error really lies in the trigger.

The existing triggers are not checking on the value i.e. not null - when the value is not null then the sequence should be incremented otherwise exit the trigger.

I have tried this re-enabling all my constraints etc and the saveorupdate method works fine with the correctly associated foreign keys.

I did say that I did not want to change the triggers - however, it would appear that there is a fundamental flaw in the logic with the triggers as they stand. I think I will therefore pursue the changes on this basis.

Thanks for all your help - just glad I have a solution!