On row insert, the primary key sequence numbers are off ... why????

[PLen]

Hello,

I an using nHibernate 2.1.2 with Oracle 10g. I have a database where
I assign the primary key to get its values from a sequence which comes
from a trigger as follows:
---------------------------
CREATE TABLE "RPT_SOURCE_GROUP"
( "GROUP_ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" NVARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "RPT_SOURCE_GROUP_PK" PRIMARY KEY ("GROUP_ID") ENABLE
)
/
CREATE OR REPLACE TRIGGER "BI_RPT_SOURCE_GROUP"
before insert on "RPT_SOURCE_GROUP"
for each row
begin
select "RPT_SOURCE_GROUP_SEQ".nextval into :NEW.GROUP_ID from
dual;
end;
----------------------------

My nHibernate mapping file (.hbm.xml) looks like:
----------------------------
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="FirstSample" xmlns="urn:nhibernate-
mapping-2.2">
<class name="FirstSample.Domain.RptSourceGroup, FirstSample"
table="RPT_SOURCE_GROUP" lazy="true" >
<id name="groupId" type="Int64" column="GROUP_ID">
<generator class="sequence">
<param name="sequence">RPT_SOURCE_GROUP_SEQ</param>
</generator>
</id>
<property name="name" column="NAME" />
</class>
</hibernate-mapping>
--------------------------------

In my code I create class object and have it added to the db via
nHibernate's command " session.Save(group);".
The output in my console looks like:

-------------------------------
NHibernate: select RPT_SOURCE_GROUP_SEQ.nextval from dual
ID = 22
NHibernate: select RPT_SOURCE_GROUP_SEQ.nextval from dual
ID = 23
NHibernate: INSERT INTO RPT_SOURCE_GROUP (ANNOTATION, ASSET, COUNTRY,
CREATED, F
RN, MEDIA_TYPE, NAME, STATUS, YEAR, GROUP_ID) VALUES
(:p0, :p1, :p2, :p3, :p4, :
p5, :p6, :p7, :p8, :p9);:p0 = NULL, :p1 = NULL, :p2 = NULL, :p3 =
1/1/0001 12:00
:00 AM, :p4 = NULL, :p5 = NULL, :p6 = 'Group 4', :p7 = 'good', :p8 =
2011, :p9 =
22
NHibernate: INSERT INTO RPT_SOURCE_GROUP (ANNOTATION, ASSET, COUNTRY,
CREATED, F
RN, MEDIA_TYPE, NAME, STATUS, YEAR, GROUP_ID) VALUES
(:p0, :p1, :p2, :p3, :p4, :
p5, :p6, :p7, :p8, :p9);:p0 = NULL, :p1 = NULL, :p2 = NULL, :p3 =
1/1/0001 12:00
:00 AM, :p4 = NULL, :p5 = NULL, :p6 = 'Group 5', :p7 = 'good', :p8 =
2012, :p9 =
23
---------------------------------

The output from nHibernate shows that it does two sequence "nextval"
calls and then it does both insert commands. The "ID=" printouts are
my debug to see what ID each of my two objects got. In this example
you can see that the first object got the number 23 and the second got
24. The problem is that when I look in the DB, the first one has an
ID of 25 and the second has one of 26. It seems that nHibernate first
asks for the next two numbers in the sequence (which are then not
added to the insert statement) and then when the row is inserted, the
DB trigger is triggered and assign the next sequence value to the
row. This creates a problem as you can imagine.

Does anyone know why this is taking place? I need my objects to have
the same ID that it has in the DB.

Any info will be greatly appreciated.

Thanks - Peter

[Fabio Maulo]

http://fabiomaulo.blogspot.com/2009/02/nh210-new-generators.html

perhaps you are looking for trigger-identity

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[PLen]

Fabio,

Thanks for the reply. I am not sure I follow, however. The hbm.xml
file lists:

<id name="groupId" type="Int64" column="GROUP_ID">
<generator class="sequence">

<param name="sequence">RPT_SOURCE_SEQ</param>
</generator>
</id>

are you saying that it should look something like:

<id name="groupId" type="Int64" column="GROUP_ID">

<generator class="trigger-identity"/>
</id>

or something to that effect?

Thanks - Peter