pl/sql trigger alter sequence

Question

I'm really new to pl/sql. I'm trying to do a Trigger like this one (adr = after delete row) but it seems that I can't use ALTER SEQUENCE. Is this the right way of decrementing after a row is deleted or should I use a procedure?

CREATE OR REPLACE TRIGGER adr_trg
AFTER DELETE ON table
FOR EACH ROW
BEGIN
ALTER SEQUENCE table_seq INCREMENT BY -1;
END;

Edit:

Sequence I'm using:

CREATE SEQUENCE table_seq INCREMENT BY 1 START WITH 1;

Trigger I'm using:

CREATE OR REPLACE TRIGGER bir_trg
   BEFORE INSERT ON table
   FOR EACH ROW
BEGIN
   IF :new.id IS NULL
   THEN
      :new.id := table_seq.nextval;
   END IF;
END bir_trg;

Answer 1

Firstly, create this function :

SQL> create or replace function fn_inc_by_minus_one( i_sch_name varchar2, i_seq_name varchar2 ) return number is   
   v_seq_no   number;
 begin
   execute immediate 'select '||i_sch_name||'.'||i_seq_name||'.nextval from dual' INTO v_seq_no;
   execute immediate 'alter sequence '||i_sch_name||'.'||i_seq_name||' increment by -2 minvalue 0';
   execute immediate 'select '||i_sch_name||'.'||i_seq_name||'.nextval from dual' INTO v_seq_no;
   execute immediate 'alter sequence '||i_sch_name||'.'||i_seq_name||' increment by 1 minvalue 0';
   return v_seq_no;
  end;
  /

whenever you need to increment your sequence by -1, just run this statement ( without ruining the original incrementing mechanism of your sequence select jsao_super_cities_seq.nextval from dual; increments by +1 as ever do ) :

 SQL> var n number;
 SQL> exec :n:=fn_inc_by_minus_one('myschema','jsao_super_cities_seq');
 /  

Answer 2

I might be "reading between the lines" here, but I suspect your aim here is that if you have 5 entries in the table populated by sequence.nextval, eg

ID
---
1
2
3
4
5

and then someone deletes row 5, you want to take the sequence back to a value of 4.

There's two things wrong with that:

1. What if someone deletes "3" ? You can't roll the sequence back by 2, because the moment you use seq=3 and move onto seq=4, you'll get a clash

2. You cannot guarantee no gaps with a sequence. All it would take is for someone to grab a sequence value, and then issue a rollback (or encounter any other kind of error in the transaction and that sequence value is gone forever)

Which brings me to the overriding question:

Why would you be worried about gaps anyway ?

Answer 3

You cannot use Alter statement inside a trigger. As per definition a trigger cannot use commit or rollback directly and "Alter" statement has auto commit. If you want you use commit or rollback inside a a trigger you can use "Autonomous transaction" block for this purpose.

Also you do not need to use trigger to insert sequence nextval into the column you can directly use seq_name.next_val inside the insert statement. decreasing the value of a sequence will create inconsistency in data for example you inserted 10 rows in the table and current value of sequence is 11. suppose now row with id= 2 is deleted and you decreased the sequence value to 10.On new insert it will either throw error if the id column is PK other wise create two rows with id = 10.