Oracle Trigger insert data with the primary key from the increase

1:
First of all, you need to 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 - 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:
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;
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'
SEX VARCHAR2 (4),
ADDRESS VARCHAR2 (40),
MEMO VARCHAR2 (60)
)

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
declare
- Local variables here
begin
select stu.nextval into: new.id 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 * /
end;
/ * Exit the sqlplus line editing * /
4, the author
commit;

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: http://blog.csdn.net/wangzwhu/archive/2008/10/29/3173918.aspx
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Oracle Trigger insert data with the primary key from the increase

  • The company first entered the company I would like to Optimize SQL project

    I was a time not long into the company of people, but also a person to be graduated, but immediately entered the project team, involved in the development. The project is to make a China Telecom Operation System, is a big system. My so-called develop ...

  • Hibernate primary key strategy-sequence

    Today, the use of hibernate in the company encountered a troublesome problem, the use of hibernate when the primary key generation strategy set sequence, but always reported in the implementation could not get next sequence value of the error, then o ...

  • Hibernate pessimistic locking mechanism for locking and optimistic locking

    hibernate lock mechanism 1. Pessimistic lock It refers to the modification of data by outsiders hold a conservative attitude. The assumption that at any time access to data, may also have another client to access the same data, in order to maintain t ...

  • hibernate using c3p0 connection pooling

    Private http://www.lifevv.com/tenyo/doc/20070605102040991.html c3p0 for open source's JDBC connection pool, with the release hibernate. This article describes how to use the hibernate configuration in c3p0. c3p0 connection pool configuration is v ...

  • Oracle instant clent for ruby / rails on cygwin

    Environment: XP: oracle full client, ruby, rails, gem cygwin: ruby rails, gem (the version with the XP version) Needs: for cygwin is installed under the rails platform support oci Steps: <1> download oracle instant client (10.2.0.3 Instant Client Pa

  • spring + hibernate + oracle9i to read and write CLOB

    Database-driven update classes12-9i.jar Hibernate modify the configuration of the following code <bean/> <bean Lazy-init="true"> <property name="nativeJdbcExtractor"> <ref local="nativejdbcExtractor"/>

  • Hibernate connection pool configuration

    Hibernate connection pool configuration <! - Jdbc -> <property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </ property> <property name="connection.url"> jdbc: oracle: thin: @ 10.203.14.132:15

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries