Sample Code for creating an auto-increment field in the Oracle database

Oracle DatabaseMediumCreate an auto-increment FieldThe method is what we will introduce in this article. We know that there is no Oracle Auto-incrementing field function in the Oracle database, but trigger and sequence are used) yes.

Assume that the [Table test] keyword segment is id. Next we will introduce the creation process:

Create a sequence with the code:

 
 

  
  
Createsequenceseq_test
  
  
Minvalue1 -- Minimum value
  
  
Maxvalue99999999999 -- maximum value
  
  
Startwith1 -- count from 1
  
  
Incrementby1 -- add a few
  
  
Nocache -- no cache
  
  
Order;
 
 

The code for creating a trigger is:

 
 

  
  
createorreplacetriggertri_test  
  
  
beforeinsertontest  
  
  
foreachrow  
  
  
declare  
  
  
nextidnumber;  
  
  
begin  
  
  
IF:new.idISNULLor:new.id=0THEN 
  
  
selectseq_test.nextval  
  
  
intonextid  
  
  
fromsys.dual;  
  
  
:new.id:=nextid;  
  
  
endif;  
  
  
endtri_test; 
 
 

Keywords:NEW and OLD use methods and meanings. new only appears in insert and update, and old only appears in update and delete. New indicates the row data to be inserted during insert, new indicates the new data to be replaced during update, and old indicates the original row to be changed, old indicates the data to be deleted.

This article describes how to create an auto-increment field in an Oracle database. We hope this article will be useful to you!