Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have many tables with a trigger attached like this

BEFORE INSERT
ON BRULES 
...snip...
  if inserting and :new.ID is NULL then
  SELECT BRULES_SEQ.nextval into :new.ID FROM DUAL;
  end if;

Due to an interesting coding style the application does inserts on some tables with a null ID which fires the trigger and uses the existing sequence. For other tables it gets the ID of the newest record and adds 1. In this case the sequence is not used and gets out of synch with the number of records in the table.

How can I tell if the next value of a sequence is valid for an insert? I could read all triggers for the sequence name and then do some dynamic SQL to compare the maximum ID and the current value of the sequence but it seems a bit clunky.

share|improve this question
    
Not an Oracle person, but doesn't oracle have some sort of auto-incrementing data type you can use instead of this? –  JNK Dec 16 '11 at 20:35
3  
The only sane answer here is "fix your app". "For other tables it gets the ID of the newest record and adds 1" this is wrong. "does inserts on some tables with a null ID which fires the trigger and uses the existing sequence" this is fine –  Jack Douglas Dec 16 '11 at 21:58
3  
If you really can't fix the app (and it is horribly broken unless it is taking exclusive locks on the table), you could consider trapping the duplicate key error and retrying ad nauseam –  Jack Douglas Dec 16 '11 at 22:01
    
I cannot agree more with Jack. You have to fix the places where the ID is not used. There is no other solution. –  a_horse_with_no_name Jan 13 '12 at 13:15
add comment

2 Answers 2

If for whatever reason you don't want to use ik_zelf's solution (preferred), you could change your sequence to use negative values. This would keep it out of the way of the Add 1 code until you can change it.

DROP SEQUENCE BRULES_SEQ;
CREATE SEQUENCE BRULES_SEQ MINVALUE -999999999999 MAXVALUE -1 
   START WITH -999999999999 INCREMENT BY 1 CACHE 20;
share|improve this answer
add comment

The values of the PK comes from different sources: horrible. Using the sequence would be the preferred way. You can not tell what exactly is the next value for the sequence by lookin in the dictionary. There is caching in effect, obviously for performance reasons. If you look in the dictionary you will find the value that is valid after the cache ran out of numbers. What you don't know, is how far the cache is used.

    drop sequence z;    
    create sequence z;  -- default cache size 20
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 1
    select z.nextval from dual; --> shows 1
    select last_number from user_sequences where sequence_name = 'Z'; --> shows 21

Can you ignore the pre entered value for the PK, so effectively only using the sequence? The way it works now is almost as bad as querying the max(pk)+1 from the table.

share|improve this answer
    
+1 for "...ignore the per-entered value for the PK, so effectively only using the sequence". Change if inserting and :new.ID is NULL then to if inserting then. –  Leigh Riffel Jan 13 '12 at 14:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.