I have a bit of a problem, I can create a person object fine with NHibernate and have an associated ID with that object which is collected by the ID Generator via a native "sequence" in the DB.


See:
<id name="Id" type="Int32" column="PERSON_ID">
<generator class="native">
<param name="sequence">PER_SEQ</param>
</generator>
</id>


I can save and commit the object to the DB, which works fine.

The problem is that the table has a trigger associated with it that increments the sequence each time a new row is inserted.

So my object gets the next sequence ID, say 5.
It assigns it to the object. Fine.
It saves and commits the object.
The DB fires a trigger, which increments the sequence, giving the actual entry an ID of 6 in the database.
My object, non-the-wiser thinks that it is still 5, but the DB has it down as 6.

Also as the sequence is being called twice, its causing the seq to jump by 2 for each insert.
Database ID's are going...2,4,6,8,10
and my objects think they are ID...1,3,5,7,9


Theres no chance I can have the trigger amended or removed as there are too many other processes and applications that rely on it.

According to some of the DBA's, I need to change my SQL statement to include "returning into" to retrieve the ID assigned on insert by the trigger.


Despite crawling the forums I've only found one entry with a similar issue:
http://forum.hibernate.org/viewtopic.php?t=980364

This is my exact same problem, and there seems to be a solution, but is problem this now resolved with NHibernate?
If so, how can I change my ID generator to expect a value returned from the DB on Insert?


I'm a bit of an Nhibernate newb, so I don't full understand the solution mentioned in:
http://forum.hibernate.org/viewtopic.php?t=980364
or how to modify the source code for NHibernate myself to implement it.



Any help would be greatly appreciate! Thanks

Hi Skyhigh,

You can find a follow-up on this issue here: http://jira.nhibernate.org:8080/jira/browse/NH-1176

Unfortunately, the patch I provided is not yet applied.

Hi folks,

I have the same issue with my project. I started a big project with nHibernate, after al mappings and entities, I found this problem.

and I am not able to build the nHibernate 1.2 GA.Src, so I can't make changes to the nHibernate.

anybody helps me will be great to me...please help me.

Hi folks,

I came with the solution for this, but I am not sure how it is working.
just drop the trigger for incrementing the key value and also drop the sequence, and give "increment" as generator class name.

It will increments automatically and works fine.

Although dropping the trigger would work....it's not viable for many projects that interact with existing databases.

Specifically the database I'm connecting to is massive, with over 100 other systems talking to it - many of which rely on and expect the trigger.

Hence, I have to work-around the trigger and take it into account, there's no way it's ever going to be altered or removed. I'd have to fight with about 11 DBA's first.


I did find a solution, which I believe was actually Tolomaüs', although it involved downloading the source, modifying it in several places, creating a new dialect built on the current Oracle 9 dialect....and then compiling and running all the tests.
It *did* work, but it's no viable since I can't go off and spend half a day fiddling with the source code modifying it to work with this issue, every time a new version of NHibernate is released.
Also theres the big risk of the base code changing so much, that the alterations stop working, or the problem can no longer be easily solved.


It would be much better to get Tolomaüs' solution wrapped into the current NHibernate code base - so that the solution is persistent, and taken into account with future development.

Another update, for anyone else who's facing this Oracle issue (sequence ID table trigger on record insert) please see:

http://jira.nhibernate.org/browse/NH-1176

Also please "watch" and "vote" for this item (you may have to create a quick account, takes 30 seconds) to help promote this item up the work list.

Although it's not a "hey, this is broken now!" critical problem, it is pretty critical to the majority of systems that want to work with existing Oracle databases, since this little trigger-trick is part of the Oracle staple diet.

Fixing it, would greatly aid NHibernates journey with those of us who have to talk to Oracle db's.

Plus Minor is a bit low, surely functionality wise its Intermediate....and its a patch, someones already written the fix! And updated it to the latest release.
Just needs a good copy and paste into the main code base.

http://jira.nhibernate.org/browse/NH-1176

Another update, for anyone else who's facing this Oracle issue (sequence ID table trigger on record insert) please see:

http://jira.nhibernate.org/browse/NH-1176

Also please "watch" and "vote" for this item (you may have to create a quick account, takes 30 seconds) to help promote this item up the work list.

Although it's not a "hey, this is broken now!" critical problem, it is pretty critical to the majority of systems that want to work with existing Oracle databases, since this little trigger-trick is part of the Oracle staple diet.

Fixing it, would greatly aid NHibernates journey with those of us who have to talk to Oracle db's.

Plus Minor is a bit low, surely functionality wise its Intermediate....and its a patch, someones already written the fix! And updated it to the latest release.
Just needs a good copy and paste into the main code base.

http://jira.nhibernate.org/browse/NH-1176

Another update, for anyone else who's facing this Oracle issue (sequence ID table trigger on record insert) please see:

http://jira.nhibernate.org/browse/NH-1176

Also please "watch" and "vote" for this item (you may have to create a quick account, takes 30 seconds) to help promote this item up the work list.

Although it's not a "hey, this is broken now!" critical problem, it is pretty critical to the majority of systems that want to work with existing Oracle databases, since this little trigger-trick is part of the Oracle staple diet.

Fixing it, would greatly aid NHibernates journey with those of us who have to talk to Oracle db's.

Plus Minor is a bit low, surely functionality wise its Intermediate....and its a patch, someones already written the fix! And updated it to the latest release.
Just needs a good copy and paste into the main code base.

http://jira.nhibernate.org/browse/NH-1176

Another update, for anyone else who's facing this Oracle issue (sequence ID table trigger on record insert) please see:

http://jira.nhibernate.org/browse/NH-1176

Also please "watch" and "vote" for this item (you may have to create a quick account, takes 30 seconds) to help promote this item up the work list.

Although it's not a "hey, this is broken now!" critical problem, it is pretty critical to the majority of systems that want to work with existing Oracle databases, since this little trigger-trick is part of the Oracle staple diet.

Fixing it, would greatly aid NHibernates journey with those of us who have to talk to Oracle db's.

Plus Minor is a bit low, surely functionality wise its Intermediate....and its a patch, someones already written the fix! And updated it to the latest release.
Just needs a good copy and paste into the main code base.

http://jira.nhibernate.org/browse/NH-1176