Explain how to use Oracle sequences and triggers

The following describes the sequence and trigger used to create a table using the Oracle Client. We will record their usage: The sequence is created and the trigger is used to implement auto-increment of the table's primary key.

First, create a sequence. The syntax format of the sequence is:

Create sequence name
[Increment by n]
[Start with n]
[{MAXVALUE/MINVALUE n | NOMAXVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the sequence value is decreased according to this step.

Start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

MAXVALUE defines the maximum value that a sequence generator can generate. The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

MINVALUE defines the minimum value that can be generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

CACHE (buffer) defines the size of the memory block that stores the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

The syntax for deleting a SEQUENCE is drop sequence name;

Assume that table A has its primary key ID. First, the incremental sequence SEQ_A is created:

Create sequence SEQ_A

Increment by 1

Start with 1

Minvalue 1 nomaxvalue

Nocylce

Then create A trigger. When there is data inserted into Table A, use the incremental primary key value of sequence for it.

Create trigger TRG_A before insert on
For each row
Begin
Select SEQ_A.nextval into: new. ID from dual;
End;

So far, the creation is complete.