Oracle Database auto-increment Field

When Oracle is used, this situation occurs. When a module is added, only one module name is input, and there is a module ID in the table. This ID is set to auto-increment. But in Orac

When Oracle is used, this situation occurs. When a module is added, only one module name is input, and there is a module ID in the table. This ID is set to auto-increment. But in Orac

When using SQL Server, an auto-increment field is added and set as the primary key to ensure that each record does not repeat. Now let go of the advantages and disadvantages of doing so. Let alone implementation problems.

When Oracle is used, this situation occurs. When a module is added, only one module name is input, and there is a module ID in the table. This ID is set to auto-increment. However, the field auto-increment function is not available in Oracle. However, this function can be implemented by writing sequences and triggers by yourself.

Let's first talk about the auto-increment function: auto-increment is nothing more than auto-increment and assign values to auto-increment fields according to certain sequence during insertion. Then we will create a sequence in Oracle and create a trigger so that at the time of insertion, the sequence increases by 1 and the auto-increment field is assigned a value.

I use PL/SQL for visual creation.

Creation sequence:

Name: sequence Name

Min Value: Minimum count

Max Value: maximum count

Star with: Start Time

Increment by: Step Size

Cache Size: Cache Sequence

When Cache is specified, oracle will prevent a set of specified size sequences in the memory in advance. After these sequences are used up, the next group will be generated, which will make the access faster, however, when the database is shut down, the next re-generation of the sequence may interrupt the sequence, rather than the genetic continuous number. It is best to specify a sequence when it is not particularly required;

If no Cache is entered, the default settings are used. When the Cache is set to 0 or nocache, a continuous sequence is generated.

Cycle: cyclic sequence. When the maximum value is reached, start from the minimum value again.

Order: ensure that the sequence generated and the request sequence are consistent. In parallel mode, if both A and B request the sequence at the same time, the serial number generated first must be returned to the user requesting the first request. This happens only on the oracle Parallel Server (not currently available), which is required in most cases.

Create a trigger:

A statement-level trigger that does not contain the foreachrow clause in the CreateTrigger statement. A statement-Level Trigger can only trigger an event once and cannot be affected by the column values of each row of the trigger. It is generally used to process information about the SQL statements that trigger the trigger with the episode trigger-for example, who will execute the SQL statements and when to execute the SQL statements.

A Row-level trigger that contains the ForEachRow clause in the CreateTrigger statement. A row-Level Trigger can be triggered on each row affected by the trigger, and can access the original column value and new column values processed through SQL statements. A typical application of a row-Level Trigger is to execute a transaction rule when you need to know the column value of a row.

After confirmation, the following picture will appear

Insert a select SEQ_MODEL.Nextval INTO: new. mdlID FROM dual;

I don't know. Have you understood the above sentence? If not, let's take a look at the supplement below.

Supplement:

1) For the Insert statement, the value to be inserted is included in new. column_name, where column_name is a column in the table.

2) for the Update statement, the original value of the column is included in old_column_name, and the new value of the data column is in new. column_name.

3) for the Delete statement, put the column value of the row to be deleted in old. column_name.