AutoIncrement.sql

AutoIncrement.sql

CREATE TABLE people (
    id  NUMBER NOT NULL, 
    name VARCHAR(56), 
    last_name VARCHAR(56), 
    dob DATE, 
    graduation_date DATE, 
    created_at TIMESTAMP, 
    updated_at TIMESTAMP)

ALTER TABLE people ADD CONSTRAINT people_pk PRIMARY KEY ( id )

CREATE SEQUENCE people_seq START WITH 1 INCREMENT BY 1

CREATE OR REPLACE TRIGGER people_trigger
BEFORE INSERT ON people REFERENCING
NEW AS new
OLD AS old
FOR EACH ROW
BEGIN
    SELECT COALESCE(:new.id, people_seq.nextval) INTO :new.id FROM dual;
END;