DBA Hub

📋Steps in this guide1/4

AutoNumber And Identity Functionality (Pre 12c)

Implement AutoNumber or Identity column behaviour in Oracle (Pre 12c).

oracle miscconfigurationintermediate
by OracleDba
12 views
1

Overview

Oracle Database 12c has introduced two new features that make the trigger-based solution for populating numeric ID columns redundant. If you are using Oracle 12c onward, you should consider one of the following options. If you are using a version of the database prior to Oracle 12c, this article describes how to implement AutoNumber/Identity type functionality in Oracle. Developers who are used to columns in MS Access or columns in SQL Server often complain when they have to manually populate primary key columns using sequences in Oracle. This type of functionality is easily implemented in Oracle using triggers. Create a table with a suitable primary key column and a sequence to support it. Create a trigger to populate the column if it's not specified in the insert.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
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;
2

Section 2

As described here , from 11g onward and sequence pseudocolumns can be used directly in PL/SQL expressions, so you could instead do the following. Test it using the automatic and manual population methods. 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 the following.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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;
/

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := dept_seq.NEXTVAL;
END;
/

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>
3

Section 3

To overwrite any values passed in you should do the following. To error if a value is passed in you should do the following. For more information see:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
BEGIN
  IF :new.id IS NULL THEN
    SELECT dept_seq.NEXTVAL
    INTO   :new.id
    FROM   dual;
  END IF;

  -- Do more processing here.
END;
/

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

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;
/
4

Section 4

Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!