This is not new but I’ve learned about it last week 🙂 from one of developers when reviewing codes with them for the plan to change the sequence’s current value. Normally I would drop the sequence and create it with START WITH to a new desired number. However, my approach will obviously invalidate all dependencies which commonly refer to triggers.
The trick is to change the INCREMENT BY value to the difference between the current value and the needed value, then use it once, finally switch the INCREMENT BY back to 1.
Here is a quick demo:
This TR_TAB_A_BI trigger depends on the SQ_TAB_A sequence.
create or replace trigger TR_TAB_A_BI before insert on TAB_A for each row when (new.id is null) begin select SQ_TAB_A.nextval into :new.id from dual; end; /
Start with trigger valid.
SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI VALID
Let’s drop trigger.
SQL> drop sequence SQ_TAB_A; Sequence dropped.
As expected, the trigger became invalid.
SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI INVALID
After the sequence is recreated, you can compile this trigger before use, or you can just execute an insert into the table, Oracle will recompile this trigger automatically before actual use.
SQL> create sequence SQ_TAB_A start with 243 cache 20; Sequence created. SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI INVALID SQL> insert into TAB_A (B) values ('AA'); 1 row created. SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI VALID
Neat!
The other way to avoid triggers being invalidated at all, as mentioned in the first paragraph, is to change the INCREMENT BY value.
SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI VALID
Assuming the current value is 300, we want a new current value to be 500. So the difference is 200.
SQL> alter sequence SQ_TAB_A increment by 200 nocache; Sequence altered. SQL> select SQ_TAB_A.nextval from dual; NEXTVAL ---------- 500 SQL> alter sequence SQ_TAB_A increment by 1 cache 20; Sequence altered.
With all changes in sequence, the trigger will remain valid.
SQL> select object_name, status from user_objects where object_name='TR_TAB_A_BI'; OBJECT_NAME STATUS -------------------- ------- TR_TAB_A_BI VALID
I gotta bookmark this site it seems handy handy.
Very helpfull. thanks
That’s a useful tip! I usually write a loop to increment the sequence. That wouldn’t work if the sequence is to be decremented, but then you don’t want that most of the time!
SQL>
SQL> select my_seq.currval from dual;
CURRVAL
———-
21
1 row selected.
SQL>
SQL> — Let’s say I want to increment this sequence value till 30
SQL>
SQL> var n number;
SQL>
SQL> exec for i in 1..(30 – 21) loop :n := my_seq.nextval; end loop;
PL/SQL procedure successfully completed.
SQL>
SQL> select my_seq.currval from dual;
CURRVAL
———-
30
1 row selected.
SQL>
SQL>