Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: Drop/create sequence from a trigger

    Hello all, first of all happy hollidays.

    Now the year is ending, I need to alter some sequences, preferably automatically.

    I've got this trigger:
    Code:
    CREATE OR REPLACE TRIGGER trg_ins_prc
     BEFORE
      INSERT
     ON process
    REFERENCING NEW AS NEW OLD AS OLD
     FOR EACH ROW
    DECLARE
        v_process_id process.process_id%TYPE;
    BEGIN
        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);
    END;
    /
    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.

    Any help, please?

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by xixo View Post
    But now, with the new year, I need to reset the sequence to start over from 1.
    Are you sure? You will get duplicate process_ids then...

    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.
    As this is a one-time (per year) thing, why not simply do it manually?
    Possibly schedule a job to be run January 1st that "resets" the sequence.

    Having said this, you cannot "reset" a sequence. You need to drop and recreate it to reset the value.

  3. #3
    Join Date
    May 2004
    Posts
    95
    thanks for the answer.

    for incredible it may seem, I didn't consider a JOB, but seeing it, it really is the best way.

    about the ID's, it won't be duplicated because it's a concatenation between the current year and the sequence value (as you can see in the code).

    said this.... thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •