How to Create Auto Increment Columns in Oracle - lifeaftercoffee.com

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.
Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');
1 row created.
SQL> INSERT INTO test (name) VALUES ('Bork');
1 row created.
SQL> INSERT INTO test (name) VALUES ('Matt');
1 row created.
SQL> SELECT * FROM test;
ID NAME
---------- ------------------------------
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

 Sources:  lifeaftercoffee.com