Oracle uses triggers to implement auto-increment of primary keys for data insertion

Article 1: First, you must have a table! CREATETABLEexample (IDNumber (4) NOTNULLPRIMARYKEY, NAMEVARCHAR (25 ),

Article 1: First, you must have a table! Create table example (ID Number (4) not null primary key, name varchar (25 ),

Article 1: First, you must have a table!

Create table example

(

ID Number (4) not null primary key,

Name varchar (25 ),

Phone varchar (10 ),

Address varchar (50)

);

Then, you need a custom sequence

Create sequence emp_sequence

Increment by 1 -- add several

Start with 1 -- count from 1

NOMAXVALUE -- do not set the maximum value

NOCYCLE -- always accumulate, not loop

NOCACHE -- do not create a buffer

The above code completes the establishment of a sequence, named emp_sequence. The range is from 1 to infinitely large (the degree of infinity is determined by your machine ), nocycle decides not to loop. If you set the maximum value, you can use cycle to make seq reach the maximum and then loop. for nocache, by the way, if you provide the cache value, the system will automatically read the seq of your cache value, which will speed up the operation during repeated operations, however, if an unexpected situation occurs, such as when the machine or Oracle is dead, the seq value retrieved next time will be inconsistent with the previous one. (If the connection is not coherent, we recommend using cache because time is money! Run the question !)

You only have tables and sequences. You still need a trigger to execute them! The Code is as follows:

Create trigger "TRIGGER name" BEFORE

Insert on example for each row when (new. id is null)

Begin

Select emp_sequence.nextval into: new. id from dual;

End;

Close the work! Next, try to insert data!

Another article:

Shortly after learning oracle, I found such a problem during table creation. For example, I am creating a table: student

Create table STUDENT

(

Id number not null,

NAME VARCHAR2 (20) default 'male ',

SEX VARCHAR2 (4 ),

ADDRESS VARCHAR2 (40 ),

MEMO VARCHAR2 (60)

)

Now I want to automatically increase the id every time a piece of data is inserted. this is a good implementation in SQLSERVER, but I have been doing it for a long time in oracle. I checked the data and found that I would use the "sequence" and "Trigger" knowledge.

First, create a sequence:

Create sequence STU

Minvalue 1

Max value 999999999999

Start with 21

Increment by 1

Cache 20;

Then, create a trigger for the table student:

Create or replace trigger stu_tr

Before insert on student

For each row

Declare

-- Local variables here

Begin

Select stu. nextval into: new. id from dual;

End stu_tr;

**************************************** **************************************** **************************************** *******

The following example shows how to create an auto-increment primary key:

1. Create a user data table

Drop table dectuser;

Create table dectuser (

Userid integer primary key,/* primary key, automatically added */

Name varchar2 (20 ),

Sex varchar2 (2)

);

2. Create an automatic growth sequence

Drop sequence dectuser_tb_seq;

Create sequence dectuser_tb_seq

Minvalue 1

Max value 99999999

Increment by 1

Start with 1;/* The step size is 1 */

3. Create a trigger

Create or replace trigger dectuser_tb_tri

Before insert on dectuser/* trigger condition: This trigger is triggered when the insert operation is performed on the table dectuser */for each row/* checks whether each row is triggered */

Begin/* trigger start */

Select dectuser_tb_seq.nextval into: new. userid from dual;/* trigger subject content, that is, the action executed after the trigger, where the next value of the obtained sequence dectuser_tb_seq is inserted into the userid field in the dectuser table */

End;/* exit sqlplus row edit */

4. Submit a commit;

Now we have finished setting the auto-increment primary key! Check it.

Insert into dectuser (name, sex) values ('wang', 'female ');

**************************************** **************************************** ********************

In general, you can add a trigger before the insert operation to put the value of the sequence into the ID of the data to be inserted.

Therefore, you need to create a table> sequence> trigger.

I think there are several notes

First, it is better to set the primary key type to INT type.

Second, in the trigger topic content, the colon following select... into cannot be dropped.