arjuna del toso

Using databases it is often useful to have rows identified by a unique numeric value (mainly for ID columns), no matter what the value is. If, like me, you have a background of MySQL (where auto increment values are easy like saying AUTO_INCREMENT) but for some reasons you have to develop something on Oracle Database 10g you’ll be a little confused about the lack of a single auto increment function.

Here is a simple howto on how to have an auto incremented numeric column value on Oracle Database using a sequence and a trigger (if you find some errors please use the comments):

Tested on: Oracle Database 10g Enterprise Edition

Precondition: a numeric type (INTEGER is ok) column in a table (in this example the schema is PROVA01 and the table is UTENTI with ID as column).

1. Log in Oracle Enterprise Manager (probably http://servername:1158/em/) as user with proper privileges (SYS as SYSDBA will work);
2. Go to “Administration”;
3. Under “Schemas > Database Objects” open “Sequence”;
4. Select proper schema (in this how to is PROVA01, the table is UTENTI and the field is ID);
5. Create a new sequence by filling:
   1. Name: choose a name, something like UTENTI_ID_SEQ should be fine;
   2. Schema: select PROVA01;
   3. Max value: no limit;
   4. Min value: enter value 1;
   5. Increment: 1;
   6. Start: 1;
   7. De-select “Use Cache”
   8. When done click “OK” button (the screenshot is in italian)
      
6. Return back to administration page and go to “Programs > Triggers” and create a new trigger:
   1. Name: TRIGGER_SEQ_UTENTI is a good choice;
   2. Schema: PROVA01;
   3. Select “Replace if exists”
   4. Trigger body (in bold values you may change):

      BEGIN
      SELECT UTENTI_ID_SEQ.NEXTVAL
      INTO   :NEW.ID
      FROM   DUAL;
      END UTENTE_SEQ_TRIGGER;

   5. Go to “Event” tab and configure the trigger:
      1. “Execute the trigger” on Table;
      2. Table: in this case PROVA01.UTENTI;
      3. Start trigger: before;
      4. Event: INSERT;
   6. Go to “Advanced” and select “FOR EACH ROW”;
7. Save the trigger and, if any, check out the errors.

Test the sequence and trigger by inserting a new table row, the ID value will be auto-inserted (and incremented).