Row Number for table in plsql

Question

I want the first column of a table in PLSQL to be auto-increment.That column will be the primary key for that table. I heard something called serialize but i didn't get proper description about that. i was working in SQL Server. I am new to Oracle(PLSQL). Please help me to find out a proper solution.

Answer 1

Create a sequence

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

Create a trigger

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.name_of_primary_key_column
    FROM dual;
END;

The syntax of the trigger gets a little simpler in 11g since you can do a direct assignment to the :new.name_of_primary_key_column rather than selecting from dual. And I understand that there is some additional syntactic sugar in 12c that makes this even easier though I haven't played around with that.