Creation of sequence in Oracle and creation of sequence triggers ____oracle

Source: Internet
Author: User
First, see if your users have increased sequence permissions

If not,
Add permissions
Conn Sys/password as Sysdba
Grant create sequence to user; orcale Add sequence:

Create sequence zhoumm– created sequence name
MinValue the minimum value from a few starts
What is the maximum value of MaxValue 999999999–?
Start with the How many starts
INCREMENT by how many to add each time
Cache 100– If connected exercise maximum cache count is 100
Order DB2 's writing:

Create sequence Seq_test
As bigint
Start with 20000
Increment by 1
MinValue 10000
MaxValue 99999999999999999
Cycle
Cache 20
Order PostgreSQL's writing:

Create sequence Seq_test
Increment by 1
MinValue 10000
MaxValue 99999999999999999
Start 20000
Cache 20
Cycle -Always loop to add sequence modifications

Modify the premise is SEQUENCE owner, or have alter any SEQUENCE permission to change SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.
An example of Alter sequence:
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXvalue 10000
cycle– to 10000 and start from scratch.
NoCache
The initialization parameters that affect sequence:
Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE simultaneously.
I can do that.
Alter sequence MYSEQ MaxValue 500
Note: Can not change the start with, other random changes (set MinValue value can not be greater than the current value) If you do not add a trigger, you need to insert * *

Insert into TABKINFO11 (instructionnumber,name) value (zhoumm.nextval, ' small white ');

This will enable the Instructionnumber field to be self added.

If you add a trigger, you can

Insert into TABKINFO11 (instructionnumber,name) value (', ' small white ');

This allows you to add a sequence trigger: (triggers that are orcale)

The name of the CREATE or replace trigger zhoumm_increate– trigger
Before insert on taskinfo11– source table
For each row
DECLARE
NextID number
BEGIN
If:new. Instructionnumber is NULL or:new. Instructionnumber = 0 THEN
SELECT zhoumm.nextval– previously established sequence
Into NextID
From Sys. DUAL;
: New. Instructionnumber = NextID;
End If;
End Zhoumm_increate; (If the previous method appears, the creation succeeds but the compile error uses the following) or

Create trigger ZhouMM11 before insert on TASKINFO11
Triggers created by –ZHOUMM11
–TASKINFO11 Table Name
For each row
Begin
Select Zhoumm.nextval into:new. Instructionnumber from dual;
–instructionnumber fields to be self-added
End How to Modify sequence permissions (assign user AAA's sequence query rights to the user BBB):

Conn AAA/AAA
Grant SELECT on Sequencename to BBB
Conn BBB/BBB
Select Aaa.sequencename.nextval from dual Grant and reclaim permissions:

Grant Select on seq_teammember_id to AAA
Revoke select on seq_teammember_id from BBB

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.