Now the year is ending, I need to alter some sequences, preferably automatically.
I've got this trigger:
CREATE OR REPLACE TRIGGER trg_ins_prc
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
SELECT prc_seq.NEXTVAL INTO v_process_id FROM DUAL;
:NEW.process_id := TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))||LPAD(v_process_id,6,0);
As you see, the sequence "prc_seq" puts the numbers ID's on table "process".
But now, with the new year, I need to reset the sequence to start over from 1.
I know I can't perform DML operations inside the trigger, and this is a "BEFORE INSERT" trigger, so I can't see a way to workaround this situation.