title: Oracle Sequence Value Increments when Unique Constraint Violated
body: <p>I created an Oracle sequence and trigger to auto-increment the primary key column on a table when new records are inserted. Here is my code:</p>
  
  <pre><code>CREATE TABLE MOBILE_APP
  (
    "MOBILE_APP_ID" NUMBER(9, 0) PRIMARY KEY,
    "NAME" VARCHAR2(60) NOT NULL,
    "DESCRIPTION" VARCHAR2(200),
    CONSTRAINT name_unique UNIQUE (name)
  );
  
  CREATE SEQUENCE MOBILE_APP_ID_SEQ
  MINVALUE 1
  MAXVALUE 999999999
  INCREMENT BY 1
  START WITH 1
  NOCACHE
  ORDER
  NOCYCLE;
  
  CREATE TRIGGER MOBILE_APP_BR_I
  BEFORE INSERT ON MOBILE_APP
  FOR EACH ROW
  BEGIN
    SELECT MOBILE_APP_ID_SEQ.NEXTVAL INTO :NEW.MOBILE_APP_ID FROM dual;
  END;
  </code></pre>
  
  <p>Since my trigger is "before insert," it will execute before a record is actually inserted into the table. But I did not expect my trigger to execute even in the case of a unique constraint violation during an insert. Let's say that the table, sequence and trigger are all new, and I try executing the statement below twice.</p>
  
  <pre><code>INSERT INTO MOBILE_APP (name, description) VALUES ('Name', 'Desc');
  </code></pre>
  
  <p>The first execution will complete successfully, auto-populating the value of 1 in the "mobile_app_id" field for the inserted record. As expected, the second execution will error out with a unique constraint violation relative to the "name" field. But if I then insert another record without violating the unique constraint, the value 3 is auto-populated in the "mobile_app_id" field for the inserted record -- meaning that during the attempted insert that failed due to a unique constraint violation, the value of the sequence still incremented from 1 to 2. How can I prevent this? I found this <a href="https://stackoverflow.com/questions/2530339/primary-key-identity-value-increments-on-unique-key-constraint-violation">other post</a>, but unfortunately it contains no solution to the problem. Any help will be greatly appreciated!</p>