Primary Key Increase by Trigger

Oracle Create Table：

CREATE TABLE TAB(
  ID NUMBER(10) NOT NULL PRIMARY KEY,
  NAME VARCHAR(19) NOT NULL
);

Drop table when exists:

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE TAB';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
       RAISE;
    END IF;
END;

Create sequence as id:

CREATE SEQUENCE ID_SEQ_TAB
START WITH 1  
INCREMENT BY 1  
MINVALUE 1  
NOMAXVALUE  
NOCACHE;

Drop sequence when exists:

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ID_SEQ_TAB';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
       RAISE;
    END IF;
END;

Create trigger:

CREATE OR REPLACE TRIGGER ID_TRI_TAB  
BEFORE INSERT   
ON TAB  
FOR EACH ROW  
WHEN(NEW.ID IS NULL)  
BEGIN  
SELECT ID_SEQ_TAB.NEXTVAL INTO :NEW.ID FROM DUAL;  
END; 

Insert into table:

 

Insert into table_name (column_name...) values(values...)

 

Select column names of a table:

select column_name from user_tab_columns
where table_name = 'YOUR TABLE NAME HERE';

 

ps:

• dual:

A mystery table in Oracle, You can select many things from this table:

Select a_squence_name.nextval from dual

• Select into from:

A copy operation on oracle.You can use it like this:

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename