An Oracle Trigger for an Auto-Increment Column

In Oracle 12c, you can now define columns as auto increment columns, similar to what you can do in SQL Server and other databases.  Ultimately, what Oracle is doing is creating a sequence in the background and wiring this up behind the scenes to the column in your table.

Here is an example of an auto incrementing column in Oracle 12c

CREATE TABLE students
(
    student_id     NUMBER(10)      GENERATED ALWAYS AS IDENTITY NOT NULL,
    first_name     VARCHAR2(30)    NOT NULL,
    last_name      VARCHAR2(30)    NOT NULL,
    email          VARCHAR2(50)    NULL,
    phone          VARCHAR2(20)    NULL,
    create_date    DATE            NOT NULL,
    modified_date  DATE            NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

However, most databases around today were designed before 12c was available.  No worries though, you can use the combination of a trigger and a sequence to synthesize this behavior in Oracle.  And in fact, people have been doing this for years, at least as long as I have been working with Oracle which goes all the way back to 1996.  So how do we do this:

First, lets consider a table of the form:

CREATE TABLE students
(
    student_id     NUMBER(10)      NOT NULL,
    first_name     VARCHAR2(30)    NOT NULL,
    last_name      VARCHAR2(30)    NOT NULL,
    email          VARCHAR2(50)    NULL,
    phone          VARCHAR2(20)    NULL,
    create_date    DATE            NOT NULL,
    modified_date  DATE            NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

And now, we need to define a sequence and a trigger:

-- Start at any number you want to - here I'll start at 100    
CREATE SEQUENCE seq_student_id_sequence START WITH 100;    
    
    
CREATE OR REPLACE TRIGGER tr_students_assign_id 
    BEFORE INSERT
    ON students
    FOR EACH ROW
    BEGIN
        :new.student_id := seq_student_id_sequence.nextval;
END;

The trigger is pretty simple.  Before a row is inserted, it will grab the next value from the sequence and use the :new psuedorow to put that value in the appropriate field, in this case student id.

As I said in my Pluralsight course, if you work with Oracle for any length of time, you are going to see triggers very similar to this in the databases you work with.  So even though we might not need to create triggers like this in a post 12c world, it is still good to understand how this works, because databases using these constructs will be around for years.