Oracle Trigger insert data with the primary key from the increase

First of all, you need to have a table!
CREATE TABLE example (

Then you need a custom sequence
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 - Every time a few
START WITH 1 - start counting from 1
NOMAXVALUE - do not set the maximum
NOCYCLE - has been cumulative, not circulation
NOCACHE - do not build above code buffer has completed a sequence of (sequence) of the building process, known as emp_sequence, range from a beginning to infinity (infinite extent is determined by your machine), nocycle was decided not to cycle If you set the maximum you can cycle to the largest circulation, would seq. For nocache By the way, if you cache value is given then the system will automatically read the value of your cache size of a seq, so that repeated operations will accelerate the speed, but if in an accident situation, such as crash or Oracle is dead, then the next value will be removed and the last seq no consistency. (If the proposal is not consistent does not matter with cache, because time is money ! beside the point!)

You have only a table and sequence is not enough, also need a trigger to run it! Code:
INSERT ON example FOR EACH ROW WHEN ( is null)
select emp_sequence.nextval into: from dual;
Once on shore, call it a day! Here you try to insert data into it!

Another one:

Learn oracle soon found in the construction of the table such an issue, for example I create a table: student

create table STUDENT
ID NUMBER not null,
NAME VARCHAR2 (20) default 'F'

Now I would like to achieve a per insert data, let id automatically increase 1. SQLSERVER in the realization of this good, but the oracle of me So now, check the following information was found to use the "sequence (sequence)", "trigger device "of knowledge.

First, create a sequence:

create sequence STU
minvalue 1
maxvalue 999999999999
start with 21
increment by 1
cache 20;

Then, create a trigger to the table student:

create or replace trigger stu_tr
before insert on student
for each row
- Local variables here
select stu.nextval into: from dual;
end stu_tr;

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

Here an example to illustrate the self-created by primary key:

1, building user data table
drop table dectuser;
create table dectuser (
userid integer primary key, / * primary key, auto increment * /
name varchar2 (20),
sex varchar2 (2)

2, Create auto-up sequence
drop sequence dectuser_tb_seq;
create sequence dectuser_tb_seq minvalue 1 maxvalue 99999999
increment by 1
start with 1; / * step 1 * /
3, create trigger
create or replace trigger dectuser_tb_tri
before insert on dectuser / * trigger: when to insert the table dectuser operation trigger this trigger * /
for each row / * test whether the trigger for each row * /
begin / * Trigger the beginning * /
select dectuser_tb_seq.nextval into: new.userid from dual; / * trigger the subject content, that is triggered after the implementation of the action, this is achieved in the next sequence dectuser_tb_seq value inserted into the table dectuser in the userid field * /
/ * Exit the sqlplus line editing * /
4, the author

Now completed by the primary key from the set, done! What can be detected.
insert into dectuser (name, sex) values ('wang', 'female');
************************************************** **************************************************

Overall, through the insert operation, add a trigger, the value of the sequence to be inserted into the ID of the data.
Therefore, the need to build the table -> Construction sequence -> build trigger such a process.
I think it should be noted that one primary key type is INT type is better.
Second, the trigger subject content, select ... into the back of the colon can not be lost.

This article comes from CSDN blog, reproduced, please indicate the source:

分类:Database 时间:2010-03-01 人气:258
blog comments powered by Disqus


  • Oracle table's primary key to achieve automatic growth 2011-07-11

    These days do Oracle, want the table's primary key automatically grow, check the network to achieve the following: create table simon_example ( id number(4) not null primary key, name varchar2(25) ) - The establishment of the sequence: -- Create sequ

  • increment to achieve the primary key oracle 2011-02-13

    Because the primary key is set to ORACLE does not automatically increase, so must be the trigger to complete the sequence and increment primary key 1, a data table create table Test_Increase ( userid number (10) primary key, / * set up primary key *

  • ORACLE create a primary key 2010-03-29

    / / Create a primary key / / create a sequence / / create trigger / / implementation of the OK alter table question add constraints pk_question primary key (id); CREATE SEQUENCE question_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE; CRE

  • Oracle in hibernate primary key to achieve self-growth strategy 2010-04-01

    In many cases, we use Hibernate in the database has been established on the basis of good. In oracle, if well-established use of the database sequence, you can follow the following steps to get it into Hibernate in: 1, first create a sequence in orac

  • mysql, oracle of the primary key from the growth 2010-07-08

    1, to define the primary key identifier type to automatically increase In mysql, if the table's primary key set to auto_increment type, the database will automatically assign the primary key. For example: create table customers (id int auto_increment

  • Oracle in hibernate primary key to achieve self-growth strategy (transfer) 2010-09-15

    In many cases, we use Hibernate in the database has been established on the basis of good. In oracle, if well-established use of the database sequence, you can follow the following steps to get it into Hibernate in: 1, first create a sequence in orac

  • oracle create a primary key auto growth 2011-07-25

    --drop table REPORT_LOG cascade constraints; /*==============================================================*/ /* Table: REPORT_LOG */ /*==============================================================*/ create table REPORT_LOG ( ID NUMBER(20) not nul

  • oracle ordered by primary key 2010-11-29

    Has encountered a problem today, I found in the application list shows one of our web special slow, the list simply order the data under a single table data output, and is create table T_ATTACKEVENT ( ATTEVNT_ID_N NUMBER not null, ATTEVNT_SIP_C VARCH

  • Walk around - jdbc oracle insert get primary key 2010-03-29

    import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; public class OracleTest { public static void main(String[] args) { Co

  • increment primary key oracle to solve the problem 2011-09-15

    sql server and mysql can set the primary key from the increase, but not from the oracle by the primary key attributes can be achieved through the following two ways: 1, to establish the sequence created by the trigger sequence to achieve <br /> CREA

iOS 开发

Android 开发

Python 开发



PHP 开发

Ruby 开发






Javascript 开发

.NET 开发



Copyright (C), All Rights Reserved. 版权所有 闽ICP备15018612号

processed in 0.232 (s). 12 q(s)