Using flip flop of trigger and sequence of sequence in Oracle to simulate and realize self adding column example

Problem: In SQL Server database, there is a field attribute of self-increasing column, which is also very convenient to use. However, this function is not available in Oracle. How to realize it?

Answer: Although there is no self-adding column in Oracle, it can be realized by trigger (trigger) and sequence (sequence).

Example:

1. Create a table

create table user  

(   

    id   number(6) not null,   

    name   varchar2(30)   not null primary key  

)  

2. Establishment of sequence SEQUENCE

create sequence user_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;  

Syntax:
CREATE SEQUENCE s_id NOMAXVALUE NOCYCLE
--INCREMENT BY 1--How many at a time
--START WITH 1--Count from 1
--NOMAXVALUE--No maximum value set
--NOCYCLE--1 straight accumulation, no cycle
-CACHE 10; --Caching the number of sequences helps to improve efficiency, but may cause hopping

3. Create triggers
Create an before insert trigger based on this table and use the SEQUENCE you just created in the trigger.

create or replace trigger user_trigger   

before insert on user  

for each row   

begin  

      select   user_seq.nextval  into:new.id from sys.dual ;   

end;  

Now you can insert the data test. I have proved that the above method is feasible.