CODEWITHSUNDEEP
oracle-databasegrailsgrails-orm
emstol
emstol

Reputation: 6206

Even id values. Tricky sequence mapping (grails oracle)

I'm writing a webapp in grails, and for the first time with oracle underneath. Every table has id generated from sequence and I'm setting id using trigger before insert as shown below:

CREATE TABLE  "RECIPE" 
   (    "ID" NUMBER(19,0) NOT NULL ENABLE, 
    ...
     PRIMARY KEY ("ID") ENABLE
   )
/

 CREATE SEQUENCE   "RECIPE_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
/

CREATE OR REPLACE TRIGGER  "BI_RECIPE" 
  before insert on "RECIPE"               
  for each row  
begin   
    select "RECIPE_SEQ".nextval into :NEW.ID from dual; 
end; 
/

I also made mapping

static mapping = {
        table 'RECIPE'
        version false        
        id column:'ID', generator:'sequence', params:[sequence:'RECIPE_SEQ']
        ...
}

And now during inserting a domain object it always has an even id value. I found out that the even ids are produced because grails gets an id value from sequence and later during real inserting to the db trigger gets an id value from sequence again and overrides those given by grails.

And what to do with this? The best solution for me would be oracle working like mysql with identity key generation strategy. Can anybody share some opinion with me?

EDIT (solution): According to the Randy's answer it's enough to add a condition in the trigger like below:

CREATE OR REPLACE TRIGGER  "BI_RECIPE" 
  before insert on "RECIPE"               
  for each row  
begin
    if :NEW.ID IS NULL then
        select "RECIPE_SEQ".nextval into :NEW.ID from dual; 
    end if;
end; 
/

Works fine. Thx Randy :)

Upvotes: 1

Views: 1057

Answers (1)

Randy
Randy

Reputation: 16677

sometimes you may use the strategy of checking if :new.id is null.

if it is null, then use the sequence, if it is not null, then use the supplied value.

the uniqueness constraint will prevent actual errors.

Upvotes: 4

Related Questions