Create auto increment field in Oracle database.

1. Create Table:
CREATE TABLE PERSON(
ID INT PRIMARY KEY,
FIRSTNAME VARCHAR2(20),
LASTNAME VARCHAR2(20)
)

2. Create Sequence:


CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

3. Create Trigger, it will insert value for column "id" automatically:


CREATE OR REPLACE TRIGGER per_id
BEFORE INSERT ON person
FOR EACH ROW
BEGIN
  SELECT seq_person.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

No comments:

Post a Comment