How to use sequences and triggers to implement ID auto-increment in Oracle (code example)

What this article brings to you is about the method (code example) of using sequences and triggers to implement ID auto-increment in Oracle. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. helped.

When designing the database, Oracle does not have a function similar to the system in SQL Server that automatically assigns IDs as primary keys. At this time, Oracle can realize the function of automatically increasing IDs through "sequences" and "triggers".

1.Create a sequence Sequence

create sequence seq_uid
　　increment by 1   
　　start with 1    
　　nomaxvalue    
　　nocycle   
　　cache 10 ;

Where: "seq_uid" represents the custom sequence name;

"start with 1" means that the sequence value starts from 1;

"increment by 1" means that the sequence value increases by 1 each time.

How to use the sequence:

select seq_uid.nextval ID from dual

In this way, you will get the next value of the sequence. Put this statement in the trigger. It can achieve a function similar to the ID auto-increment in SQL Server.

2.Create trigger Trigger

create trigger tri_uid before insert on [tablename] for each row when (new.[columnname] is null)
begin
    select seq_uid.nextval into:new.[columnname] from dual;
end;

Where: "tri_uid" means self The defined trigger name;

"seq_uid" represents the sequence name to be used;

"[columnname]" represents the column to be auto-incremented;

"[tablename ]" indicates the data table where the column to be auto-incremented is located.

The above is the detailed content of How to use sequences and triggers to implement ID auto-increment in Oracle (code example). For more information, please follow other related articles on the PHP Chinese website!