|
Creating An Autonumber Field
By Budi Raharjo
http://mbraharjo.blogspot.com
Not like in some of RDBMS’s (e.g. Microsoft SQLServer), there are no feature in
Oracle to create an autonumber field automatically. What is an autonumber field?
Autonumber field is a field of tables which its value is inserted automatically
when we add a new record into the tables. However, there are many tricky ways to
do that job in Oracle. Here, I will show you the simple steps to do it using a
sequence object and a BEFORE INSERT trigger.
Please follow these steps:
Step 1
Create a sample table using the following code:
SQL> create table sample (
2 no number not null,
3 description varchar2(200),
4 constraint pk_sample primary key(no)
5 );
Table created.
Step 2
Create a sequence object using the following code:
SQL> create sequence seq_sampleno
2 minvalue 1
3 start with 1
4 increment by 1;
Sequence created.
Step 3
Create a BEFORE INSERT trigger on sample table. Why BEFORE INSERT event? Because
we need to take a sequence object’s value and then assign the value into a :new.no
reference before Oracle actually insert a new record on sample table. Here is
the code:
SQL> create or replace trigger tr_bi_sample
2 before insert on sample
3 for each row
4 begin
5 select seq_sampleno.nextval into :new.no from dual;
6 end;
7 /
Trigger created.
Step 4
Now, insert some new records into sample table using the following code:
SQL> insert into sample(description) values('First
description');
1 row created.
SQL> insert into sample(description) values('Second description');
1 row created.
SQL> insert into sample(description) values('Third description');
1 row created.
To see the result, you can write the following code:
SQL> col no format 999 heading 'NO'
SQL> col description format A45 heading 'DESCRIPTION'
SQL> select * from sample;
Here is the result of that code:
NO DESCRIPTION
---- ---------------------------------------------
1 First description
2 Second description
3 Third description
|
