Oracle trigger to create an autonumber

Question

I have never created a trigger in Oracle before so I am looking for some direction.

I would like to create a trigger that increments an ID by one if the ID isnt in the insert statement.

The ID should start at 10000, and when a record is inserted the next ID should be 10001. If the insert statement contains a ID, it should override the auto increment.

ie

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

should look like:

firstname lastname id

Micahel Jordan 10000

Larry Bird 10001

insert into t1 (firstname, lastname, id) values ('Scottie','Pippen',50000)

should look like:

firstname lastname id

Micahel Jordan 10000

Larry Bird 10001

Scottie Pippen 50000

Answer 1

Something like this will work on 11g

CREATE SEQUENCE t1_id_seq 
  start with 10000 
  increment by 1;

CREATE TRIGGER trigger_name
  BEFORE INSERT ON t1
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.id IS NULL )
  THEN
    :new.id := t1_id_seq.nextval;
  END IF;
END;

If you're on an earlier version, you'll need to do a SELECT INTO to get the next value from the sequence

CREATE TRIGGER trigger_name
  BEFORE INSERT ON t1
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.id IS NULL )
  THEN
    SELECT t1_id_seq.nextval
      INTO :new.id
      FROM dual;
  END IF;
END;

Be aware that Oracle sequences are not gap-free. So it is entirely possible that particular values will be skipped for a variety of reasons. Your first insert may have an ID of 10000 and the second may have an ID of 10020 if it's done minutes, hours, or days later.

Additionally, be aware that Oracle does not support specifying multiple rows in the VALUES clause as MySQL does. So rather than

insert into t1 (firstname, lastname) values ('Michael','Jordan'),('Larry','Bird')

you'd need two separate INSERT statements

insert into t1 (firstname, lastname) values ('Michael','Jordan');
insert into t1 (firstname, lastname) values ('Larry','Bird');