Use of sequence while adding and deleting from a table

Question

I am creating a table where I will add file name and many other fields. I used a fileid column to represent files in sequential order; i.e. first file to be uploaded should have fieldid 1, then the next file would have fileid 2 and so on. I used a sequence and trigger:

create sequence create_file_id start with 1 increment by 1 nocache;

The trigger is:

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

But, if any record/records is/are deleted from the table, then the sequence gets jumbled. So, I am thinking of using another sequence with a trigger to decrement the value of former sequence by the number of rows deleted. But I am stuck in implementing the trigger of this sequence.

Sequence:

create sequence del_file_id increment by -1 nocache;

Any way of achieving this?

Answer 1

You can let the sequence do the primary key job and create a view of your base table, selecting

rownum as the column where you want to see numbers from 1 to 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  for each row
  4  begin
  5    :new.tab_id := gen_id.nextval; -- This kind of assignment is allowed in 11g  
  6  end;                             -- and higher, in version prior to 11g 
  7  /                                -- conventional select statement is used

Trigger created

SQL> insert into your_table(col)
  2  select level 
  3    from dual
  4  connect by level <=7;

7 rows inserted

SQL> commit;

Commit complete

SQL> select *
  2    from 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  as
  3  select tab_id
  4       , col
  5       , rownum as num
  6    from your_table
  7  ;

View created

SQL> select *
  2    from 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 from your_table where tab_id in (3,5,6);

3 rows deleted

SQL> commit;

Commit complete

SQL> select *
  2    from your_table;

    TAB_ID        COL
---------- ----------
         1          1
         2          2
         4          4
         7          7

SQL> select *
  2    from v_your_table;

    TAB_ID        COL        NUM
---------- ---------- ----------
         1          1          1
         2          2          2
         4          4          3
         7          7          4

SQL>