AutoNumber And Identity Functionality

AutoNumber And Identity FunctionalityDevelopers who are used to AutoNumber columns in MS Access or Identity columns in SQL Server often complain when they have to manually populate primary key columns using sequences. This type of functionality is easily implemented in Oracle using triggers.First we create a table with a suitable primary key column and a sequence to support it:

CREATE TABLE departments (
ID NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(50) NOT NULL);

ALTER TABLE departments ADD (
CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Next we create a trigger to populate the ID column if it's not specified in the insert:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/
Finally we can test it using the automatic and manual population methods:

SQL> INSERT INTO departments (description)
2 VALUES ('Development');
1 row created.

SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
1 row selected.

SQL> INSERT INTO departments (id, description)
2 VALUES (dept_seq.NEXTVAL, 'Accounting');
1 row created.

SQL> SELECT * FROM departments;
ID DESCRIPTION
---------- --------------------------------------------------
1 Development
2 Accounting
2 rows selected.

SQL>The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT NVL(:new.id, dept_seq.NEXTVAL)
INTO :new.id
FROM dual;
-- Do more processing here.
END;
/

To overwrite any values passed in you should do the following:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

To error if a value is passed in you should do the following:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
IF :new.id IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
ELSE
SELECT dept_seq.NEXTVAL
INTO :new.id
FROM dual;
END IF;
END;
/