oracle - Use of sequence while adding and deleting from a table -

i creating table add file name , many other fields. used fileid column represent files in sequential order; i.e. first file uploaded should have fieldid 1, next file have fileid 2 , on. used sequence , trigger:

create sequence create_file_id start 1 increment 1 nocache; 

the trigger is:

before insert on add_files_details each row begin select create_file_id.nextval :new.file_id dual; end; 

but, if record/records is/are deleted table, sequence gets jumbled. so, thinking of using sequence trigger decrement value of former sequence number of rows deleted. stuck in implementing trigger of sequence.

sequence:

create sequence del_file_id increment -1 nocache; 

any way of achieving this?

you can let sequence primary key job , create view of base table, selecting

rownum column want see numbers 1 n in sequential order:

sql> create table your_table(   2    tab_id number primary key,   3    col    number   4  )   5  ;  table created  sql> create sequence gen_id;  sequence created  sql> create trigger tr_pk_your_table   2  before insert on your_table   3  each row   4  begin   5    :new.tab_id := gen_id.nextval; -- kind of assignment allowed in 11g     6  end;                             -- , higher, in version prior 11g    7  /                                -- conventional select statement used  trigger created  sql> insert your_table(col)   2  select level    3    dual   4  connect level <=7;  7 rows inserted  sql> commit;  commit complete  sql> select *   2    your_table;      tab_id        col ---------- ----------          1          1          2          2          3          3          4          4          5          5          6          6          7          7  7 rows selected  sql> create or replace view v_your_table   2    3  select tab_id   4       , col   5       , rownum num   6    your_table   7  ;  view created  sql> select *   2    v_your_table;      tab_id        col        num ---------- ---------- ----------          1          1          1          2          2          2          3          3          3          4          4          4          5          5          5          6          6          6          7          7          7  7 rows selected  sql> delete your_table tab_id in (3,5,6);  3 rows deleted  sql> commit;  commit complete  sql> select *   2    your_table;      tab_id        col ---------- ----------          1          1          2          2          4          4          7          7  sql> select *   2    v_your_table;      tab_id        col        num ---------- ---------- ----------          1          1          1          2          2          2          4          4          3          7          7          4  sql>