title: Oracle autoincrement with sequence and trigger issue
body: <p>I've got a weird situation here. I've used triggers and sequences to implement auto-increment. I insert the data into my tables from my web app which uses Hibernate. I test the web app in my machine (Netbeans) as well as on my office network (the web app is also deployed on our server with Wildfly).</p>
  
  <p>It has always worked fine, until I started getting exceptions due to the unique constraint (Primary key). Then I discovered that the problem was the sequence that generates values for the ids. Example, For my table xtable, its sequence's last_number is 78400, the max id in xtable is 78308, but the sequence's nextval is 78304. I have no idea how that happens because I created the sequence with the following:</p>
  
  <pre><code>   CREATE SEQUENCE  XTABLE_SEQUENCE  INCREMENT BY 1 START WITH 1;
  </code></pre>
  
  <p>I tried the following to update the sequence and make its NEXTVAL greater than the max(id) in the table, but I'm still getting the same result after n inserts</p>
  
  <pre><code>declare
  maxval number(10);
  begin
     select max(ID) into maxval from XTABLE;
  maxval := maxval+1;
  execute immediate 'DROP SEQUENCE XTABLE_SEQUENCE';
  execute immediate 'CREATE SEQUENCE XTABLE_SEQUENCE START WITH '|| maxval+50 ||' INCREMENT BY 1'; 
  end;
  </code></pre>
  
  <p>Here is the trigger statement:</p>
  
  <pre><code>create or replace TRIGGER xtable_sequence_tr
  BEFORE INSERT ON xtable FOR EACH ROW
  WHEN (NEW.id IS NULL)
  BEGIN
  SELECT xtable_sequence.NEXTVAL INTO :NEW.id FROM DUAL;
  END;
  </code></pre>
  
  <p>Or what is the proper way to implement autoincrement  in Oracle in order to avoid  the issue I am facing? At some point, I start getting unique key constraint violation on the primary key due to the fact that (I don't know for what reason) the max id in the table happens to be greater than the sequence.nextval used in the trigger. What is causing that and how to fix it?</p>