I have a problem with the received primary key when creating a new post in the database (using ISession->Save() or ISession->SaveOrUpdate()). The primary key in the created object is not the same as the key in the database (which is a Firebird database), the key in the database' table is always one higher than the key in by object. For instance: I debug my application, and when the Save()-method has executed I look into the object and find that SignId is 565, but when I check the database (after a commit) the id is 566.

The id part in the mapping XML-file look like this:
<id name="SignId" column="signid" type="int" unsaved-value="0">
<generator class="sequence">
<param name="sequence">signid_generator</param>
</generator>
</id>

And the generator declaration in the database' table is:
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.SignId = gen_id(signid_generator, 1);
end

Does anyone know what is wrong?

Regards

The trigger, it should look like (psedocode, do not know the firbird syntax):
ACTIVE BEFORE INSERT POSITION 0
as
begin
if new.SignId IS NULL then
new.SignId = gen_id(signid_generator, 1);
end

Hello,

Isn't the problem rather the fact that you're using a create-trigger at the same time as you're using a NHibernate-generator?

When you define a NHibernate generator, NHibernate will get the next sequence value, put it in the object and then save the object in the database (which will increase the id in the database once more).

I don't know if NHibernate has a way to cope with trigger-assigned id's, I myself always use the FireBird sequences but I agree this is a risk if you allow other applications to write to your database through ODBC for example (create triggers ensure the id's are being filled in).
You might consider using generator class="assigned", assign a dummy id to your class before saving it and immediately afterwards retrieve it back from the database. But this is really not an elegant workaround.

Maybe someone else on the forum knows of a better way.

X.
Please rate this post if it helped.

On second thought, "assigned" has also a risk : if the dummy-id you used already exists in the database, NHibernate will do an update instead of an insert.
So you should use values that will never exist in the database (f.e. negative values). But even then, NHibernate might first do a SELECT on the database to see if the object already exists before saving it (requires 1 more SQL statement).

Looking at the previous pseudocode from Gert I think that might well be the best solution : check in the trigger if the id is null or zero, if it is (row is inserted through ODBC for example): generate a new one. Otherwise: do nothing.

X.
Please rate this post if it helped.

You could always write a custom Id generator class that calls your stored procedure and use that in your mapping.

benster

Thanks gert, now it works like a charm. The actual Firebird-code is:


See http://www.firebirdsql.org/manual/generatorguide-rowids.html

Regards