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;

This looks like a bizarre requirement. Why would one want to decrement a sequence on a delete statement? — Kaushik Nayak, Feb 28 '18 at 04:56

score 5 · Accepted Answer · edited Feb 28 '18 at 07:00

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:

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
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 ?

Ok, thanks, I didn't think on those scenarios. I won't worry about gaps :) — Sebastián, Feb 28 '18 at 16:49

score 1 · Answer 2 · answered Feb 28 '18 at 04:51

1

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.

answered Feb 28 '18 at 04:51

Pawan Rawat

495
1
7
25

1

Better call it "implicit commit" instead of "auto commit" – Wernfried Domscheit Feb 28 '18 at 06:46
You are right, I did't think about that scenario, thanks. – Sebastián Feb 28 '18 at 12:22
I need that trigger to make Insert Service work, I'm learning ORDS also. http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/ords/r30/Insert_Update_Delete/Insert_Update_Delete.html#section3 – Sebastián Feb 28 '18 at 12:28
If you are on a recent version of Oracle (12.1 or later) just define it as an [identity column](https://stackoverflow.com/a/11296469/230471) and forget about it. – William Robertson Mar 03 '18 at 12:14

Barbaros Özhan · Answer 3 · 2018-02-28T18:41:32.503

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

This isn't working as intended. Actually I had to drop my sequence and create a new one because of some errors. — Sebastián, Feb 28 '18 at 16:51
@Sebastián excuse me, i completely changed the logic, now it gives the true result. — Barbaros Özhan, Feb 28 '18 at 17:47

pl/sql trigger alter sequence

3 Answers3