CODEWITHSUNDEEP
oracle-databaseinsertoracle10gauto-increment
oracle
oracle

Reputation: 13

Oracle database table insertion

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?

Upvotes: 1

Views: 282

Answers (3)

tuinstoel
tuinstoel

Reputation: 7306

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.

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

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;
/

Upvotes: 8

David Aldridge
David Aldridge

Reputation: 52336

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

Upvotes: 2

Related Questions