CODEWITHSUNDEEP
oracle-databaseoracle-apex
Mateja K
Mateja K

Reputation: 57

Auto increment item in Oracle APEX

I want to make my item id auto increment, so that user cannot insert any number. I have created a sequence and a trigger.

create sequence t1_seq start with 1 increment by 1 nomaxvalue; 


create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

But, while inserting id-s, auto increment goes somenthing like: 1, 41, 52... Why it isn't going 1,2,3...

Upvotes: 0

Views: 2238

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18695

First the answer. What is your "create sequence" ddl statement ? If you create a sequence with the statement 

CREATE SEQUENCE t1_seq;

Then oracle will take a CACHE value of 20. Create the sequence with NOCACHE option if you want all the numbers to follow. But... do you really need consecutive numbers ? A sequence guarantees that every number is higher than the previous one and unique. In reality you probably don't care if there are gaps, it just looks a bit off but does that matter.

That was the answer to your question. But ... if you're on 12C or higher, I suggest you use "IDENTITY COLUMNS" instead. In that case all the work is done in the background. No trigger needed, no sequence needed. Check the docs for syntax

Upvotes: 2

Related Questions