Oracle Auto Increment: Sequence & Trigger Guide

1 year ago
Liam
1 minute

In Oracle, sequences and triggers can be used to mimic the functionality of auto-increment columns.

  1. Create a sequence:
CREATE SEQUENCE table_name_seq
START WITH 1
INCREMENT BY 1
  1. Create trigger:
CREATE OR REPLACE TRIGGER table_name_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
   SELECT table_name_seq.NEXTVAL
   INTO :new.id
   FROM dual;
END;

In the example above, table_name is the name of the table where you want to add an auto-increment column, and id is the name of the auto-increment column. By creating a sequence and trigger, the trigger will automatically assign a unique value to the auto-increment column every time a new record is inserted.

bannerAds