Hi,

How should I configure the id generator when using an insert trigger with Oracle? I cannot use the "sequence" generator as the trigger is retrieving the nextval for me prior to the insert:

BEFORE INSERT ON xxx.TRADES
FOR EACH ROW
BEGIN
SELECT SEQ_TRADES.NEXTVAL
INTO :NEW.TRADE_ID
FROM DUAL;
END TRG_TRADES;


The Hibernate doc suggests a "select" generator:

<id name="id" type="long" column="person_id">
<generator class="select">
<param name="key">socialSecurityNumber</param>
</generator>
</id>

But NHibernate doesn't seem to support it, returning the exception on the assembly load:

NHibernate.MappingException: could not instantiate id generator for strategy 'select' ---> System.TypeLoadException: Could not load type select from assembly NHibernate...

Anyone have any guidance for the situation?

Thanks,
Steve

How about
<generator class="assigned" />

So NHibernate does not generate id for you. You probably need to set id value to some value like "0".

However, I personally don't think it is a good Idea to use Triggers to generate Id's.

Hibernate In Action book has some more information about using triggers.

I probably should have specified in the original post that the use of insert triggers is a matter of policy. So *not* using them isn't an option. If I were to use an "assigned" generator the database will still generate its own id's and ignore whatever I ask it to do.

After researching this problem further, it seems like getting id's returned when using insert triggers is a fairly common problem. The only viable solution seems to be to modify the NHibernate source extending the OracleDialect class to override SupportsIdentitySelectInInsert(), which returns true if I can append something to the insert statement that will allow me to return an id. And AddIdentitySelectToInsert() which is the "something" that I would append to the insert statement.

I'm still in the middle of working this out, so if anyone else has taken this approach and is willing to share the solution you'd be saving me a great deal of experimentation.

Steve