How to verify that next sequence value can be used as a new primary key

oracleoracle-11g-r2

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.

Best Answer

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.

Related Question