Use triggers (trigger) and sequence (sequence) simulations in Oracle to implement a self-adding instance _oracle

Problem: In the SQL Server database, it is also convenient to use the field properties of the self added column. And in Oracle, there is no such function, how to achieve it?

A: Although there are no trigger in Oracle, they can be implemented by means of triggers () and sequences (sequence).

Example:

1, establish the table

Copy Code code as follows:

CREATE TABLE User
(
ID Number (6) is not NULL,
Name VARCHAR2 NOT NULL primary key
)

2. Establish sequence sequence

Copy Code code as follows:

Create sequence User_seq increment by 1-start with 1 MinValue 1 MaxValue 9999999999999 the order;

Grammar:
CREATE SEQUENCE s_id Nomaxvalue nocycle
--increment by 1--add a few at a time
--start with 1--counting starting from 1
--nomaxvalue--Do not set maximum value
--nocycle--Cumulative, not cyclic
--cache 10; --The number of cache sequences helps to improve efficiency, but may result in a jump number

3. Create triggers
create a before insert trigger that is based on the table, and use the sequence you just created in the trigger.

Copy Code code as follows:

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

You can insert the data test below. As I have shown, the above method is feasible.