Oracle database table insertion

Question

I have two tables:

create table Number( num number(5));

create table Entry(id number(3), name varchar(50));

How can I increment the num field of Number table in Oracle whenever I insert something in the Entry table?

Answer 1

Do you want number.num to continually represent the number of rows iin the Entry table? If so you could just define it as a view:

create view number_view
as
select count(*) from Entry

Answer 2

You should use a SEQUENCE instead. The "Number" table is an inherently bad idea, because when two sessions are inserting rows concurrently, each session only sees the uncommited value in the Number table.

This is what you should do instead:

create sequence entrySeq;

create table Entry(id number(3), name varchar(50));

create trigger tr_entry before insert on Entry for each row
begin
  select entrySeq.nextval into :new.number from dual;
end;
/

Answer 3

create sequence entrySeq;

create table Entry(id number(3), name varchar(50));

insert into Entry value (entrySeq.nextval, 'MyName');

(You don't need a trigger).

A sequence returns a unique and increasing number value but Oracle doesn't guarantuee that it is gapless. When sometimes transactions are rollbacked the values of column id will contain gaps.