Sequence and trigger usage in Oracle

Summarize the sequence and trigger used, and use some of the corresponding actions. The problems encountered are more wonderful, for reference only.

I. Sequence part (on-line)

1 , set up a sequence SEQUENCE

Create sequence User_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

Grammar:

CREATE SEQUENCE s_id Nomaxvalue nocycle

--increment by 1--add a few each time

--start with 1-counting starting from 1

--nomaxvalue--Do not set the maximum value

--nocycle--keep accumulating, not looping

--cache 10; --Number of cache sequences, which can help improve efficiency, but may cause jump numbers

Example: Create sequence Sequencename increment by 1 start with 1 minvalue 1 nomaxvalue nocycle;

2 , query serial number

Must be logged in as an administrator; it's not necessary .

Sequence_owner must be uppercase, regardless of whether your user name is uppercase or not. Only uppercase can be recognized. User name is case-sensitive is true

--View all sequences of the current user

Select Sequence_owner,sequence_name from Dba_sequences where sequence_owner= ' username ';

--Query The total number of sequences for the current user

Select count (*) from Dba_sequences where sequence_owner= ' username ';

3 , get the sequence of SQL Statement

Select Seq_newsid.nextval from Sys.dual;

4 , deleting a sequence of SQL

DROP SEQUENCE Seq_newsid;

Second, the trigger part (on-line)

1 , creating triggers

Create a before insert trigger that is based on the table, using the sequence you just created in the trigger.

Copy the 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 User_trigger;

2 , check all_triggers table gets trigger_name

Select Trigger_name from all_triggers where table_name= ' XXX ';

3 , according to trigger_name query out trigger details  

Select text from All_source where type= ' TRIGGER ' and name= ' tr_xxx ';

4 , delete triggers

DROP TRIGGER trigger_name;

Iii. the whole process (online)

1 , establish a table

Copy the code code as follows:

CREATE TABLE User

(

ID Number (6) is not NULL,

Name VARCHAR2 (+) NOT NULL primary key

)

2 , set up a sequence SEQUENCE

Copy the code code as follows:

Create sequence User_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

Grammar:

CREATE SEQUENCE s_id Nomaxvalue nocycle

--increment by 1--add a few each time

--start with 1-counting starting from 1

--nomaxvalue--Do not set the maximum value

--nocycle--keep accumulating, not looping

--cache 10; --Number of cache sequences, which can help improve efficiency, but may cause jump numbers

3 , creating triggers

Create a before insert trigger that is based on the table, using the sequence you just created in the trigger.

Copy the 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 User_trigger;

Iv. Practical operation (self-made)

Create a sequence

Create sequence Seq_ldtjxxb increment by 1 start with 1 minvalue 1 nomaxvalue nocycle;

Create a Trigger

The following paragraph does not work:

Create or replace trigger TRIGGER_LDTJXXB before insert on LDTJXXB

For each row

Begin

Select Seq_ldtjxxb.nextval into:new. OBJECTID from dual;

End

The following paragraph can:

Create or Replace Trigger TRIGGER_LDTJXXB

--before

Before insert on LDTJXXB

For each row

Begin

Select Seq_ldtjxxb.nextval into:new. OBJECTID from dual;

End

Running the above section in the Plsql does not meet my needs, specifically, when inserting data using a program, an error occurs, and the " trigger is invalid and is not re-verified " is shown.

In the time of Baidu found this http://bbs.csdn.net/topics/391842678, but and my situation is not the same, the use of 2 floor code, surprised to find no error, it is so wonderful, please forgive me for the moment has not been able to understand, began to suspect that Brother's insertion program is poisonous.

Sequence and trigger usage in Oracle