managing/implementing auto-increment primary key in oracle without triggers

0
votes

We have many tables in our database with autoincrement primary key ids setup the way they are in MySQL since we are in the process of migrating to Oracle from MySQL.

Now in oracle I recently learned that implementing this requires creating a sequence and a trigger on the id field for each such table. We have like 30 -40 tables in our schema and we want to avoid using database triggers in our product, since management of database is out of scope for our software appliance.

What are my options in implementing the auto increment id feature in oracle... apart from manually specifying the id in the code and managing it in the code which would change a lot of existing insert statements.

... I wonder if there is a way to do this from grails code itself? (by the way the method of specifying id as increment in domain class mapping doesnt work - only works for mysql) Some info about our application environement: grails-groovy, hibernate, oracle,mysql support

2
oraclegrailsgroovyoracle11gauto-increment
What management tasks do you expect to introduce by creating triggers and sequences? Essentially it is identical what is done in MySQL autoincrement but you build it yourself. – Rob van Laarhoven
how often would it be that a trigger fails.. – pri_dev
Depends on the code inside. I've never seen a trigger fail for other reasons than problems with the sequences, wich causes failures in the other solution too. Advantage of using a trigger is that the PK generation mechanism not only works for you app but for direct inserts using SQL or other ways/apps inserting data. I often combine the two solutions. Let hibernate do the PK generation in my app and use a trigger that generates a PK when no PK is provided. – Rob van Laarhoven

2 Answers

1
votes

This answer will have Grails/Hibernate handle the sequence generation by itself. It'll create a sequence per table for the primary key generation and won't cache any numbers, so you won't lose any identifiers if and when the cache times out. Grails/Hibernate calls the sequence directly, so it doesn't make use of any triggers either.

1
votes

If you are using Grails hibernate will handle this for you automatically.

You can specify which sequence to use by putting the following in your domain object:

static mapping = {
    id generator:'sequence', params:[sequence:'MY_SEQ']  
}