This manual describes how to adjust the value of a sequence without dropping it. The solution is to set the INCREMENT BY value to the difference between the current value and set the value required. Then you have to use this value once and then switch back to INCREMENT BY = 1
Example:
The TR_TAB trigger depends on SQ_TAB_A sequence.
|
1 2 3 4 5 6 7 8 9 10 |
create or replace trigger TR_TAB 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 valid trigger.
|
1 2 3 4 5 |
SQL> select object_name, status from user_objects where object_name='TR_TAB'; OBJECT_NAME STATUS --------------- ------- TR_TAB VALID |
Now drop the trigger.
|
1 2 3 |
SQL> drop sequence SQ_TAB_A; Sequence dropped. |
As expected the trigger becomes invalid.
|
1 2 3 4 5 |
SQL> select object_name, status from user_objects where object_name='TR_TAB'; OBJECT_NAME STATUS -------------------- ------- TR_TAB INVALID |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
After the sequence has been re-created, you can compile these, or simply perform an insert in the table. Oracle will then compile the trigger itself. 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'; OBJECT_NAME STATUS --------------- ------- TR_TAB 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'; OBJECT_NAME STATUS --------------- ------- TR_TAB VALID |
Another way to avoid that is to be INVALID triggers the INCREMENT BY to pass. Value to
|
1 2 3 4 5 |
SQL> select object_name, status from user_objects where object_name='TR_TAB'; OBJECT_NAME STATUS --------------- ------- TR_TAB VALID |
We assume that the current value is 300, we want that the new value is 500, so the difference is 200.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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. |
The trigger is VALID
|
1 2 3 4 5 |
SQL> select object_name, status from user_objects where object_name='TR_TAB'; OBJECT_NAME STATUS -------------------- ------- TR_TAB VALID |
Please let me know if this manual ‘change sequence without dropping’ was usefull to you. If there are errors or you have suggestions regarding this manual, please let me know.
No rights can be derived from this manual
Regards,
Maarten
