Way to get GORM/Hibernate to work with trigger that sets primary key

Question

I have an existing Oracle database that sets the primary key for an insert via a trigger.

TRIGGER SET_schedtemplate_id_template
 BEFORE INSERT
 ON schedtemplate
 FOR EACH ROW
 BEGIN
 SELECT schedtemplate_id_template_SEQ.NEXTVAL
  INTO :NEW.id_template
  FROM DUAL;
 END;

We have other applications that depend on this approach for this database

I want to be able to map this database in GORM in my domain object

static mapping = {
    autoTimestamp true
    table 'schedtemplate'
    version false
    id column: 'id_template', generator: 'sequence', params: [sequence: 'SCHEDTEMPLATE_ID_TEMPLATE_SEQ']
}

The problem with this approach is that GORM increments the sequence to say 12 but then on insert the sequence gets incremented again to 13. This means other objects in the object graph violate foreign key constraints as they are using GORM's 12 instead of the trigger's 13.

It appears the hibernate setting hibernate.jdbc.use_get_generated_keys = true was developed for this purpose.

How do I configure GORM/Grails to use this setting?

Answer 1

The trigger assigned identity column in Hibernate was discussed here hibernate and DB triggers

Now there is a question, how to configure it in GORM.

Try to use the custom identity generator described above like this :

    static mapping = {
      ...
      id column: 'id_template', generator: 'jpl.hibernate.util.TriggerAssignedIdentityGenerator'
    }