Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: create or replace trigger.............

    I have table AB:
    AB_ID VARCHAR2(32) PRIMARY KEY
    BF_ID VARCHAR2(8)
    FS_ID VARCHAR2(8)

    I'd like to create a trigger that insert automatically AB_ID when I insert a new BF_ID and FS_ID
    (AB_ID=BF_ID.FS_ID+1)

    FOR EXAMPLE:
    AB_ID...........BF_ID............FS_ID
    001.346..........001..............345
    023.124..........023..............123
    012.002..........012..............001

    I tried this:
    CREATE OR REPLACE TRIGGER AB_PREINSERT
    BEFORE INSERT ON AB
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    Begin
    select to_number(:NEW.BF_ID)||'.'||TO_NUMBER(:NEW.FS_ID)+ 1
    into :NEW.AB_ID
    from AB;
    End;


    ORA-01722: INVALID NUMBER
    ORA-06512: at "AB_PREINSERT", line 2

    How can I create the trigger to get this code AB_ID?
    Must I create a sequence??

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Would this work???

    CREATE OR REPLACE TRIGGER AB_PREINSERT
    BEFORE INSERT ON AB
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    DECLARE
    V_FS_ID NUMBER;

    Begin
    V_FS_ID := (TO_NUMBER(:NEW.FS_ID))+1;
    select RTRIM(:NEW.BF_ID)||'.'||V_FS_ID
    into :NEW.AB_ID
    from AB;
    End;
    /

    Gregg

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: create or replace trigger.............

    I think you want:

    CREATE OR REPLACE TRIGGER AB_PREINSERT
    BEFORE INSERT ON AB
    FOR EACH ROW
    Begin
    :NEW.AB_ID := :NEW.BF_ID||'.'||to_char(to_number(:NEW.FS_ID) + 1);
    End;

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    OK, but if FS_ID=001 I get 2 but not 002

    I tried with

    :NEW.AB_ID := :NEW.BF_ID||'.'||TO_CHAR(TO_NUMBER(LPAD(SUBSTR(:NE W.FS_ID,1),3,'0')+1));

    but I get same values.

    Have you any idea?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    True. So try this:

    :NEW.AB_ID := :NEW.BF_ID||'.'||ltrim(to_char(to_number(:NEW.FS_I D) + 1,'000'));

  6. #6
    Join Date
    Jul 2002
    Posts
    227
    OK, thanks.
    but now I'd like to get the last value with autoincrement number

    FOR EXAMPLE:
    AB_ID...........BF_ID............FS_ID
    001.345.1.........001..............345
    023.123.2.........023..............123
    012.001.3.........012..............001
    AAA.CCC.4.........AAA..............CCC
    A12.V34.5.........A12..............V34

    I tried with this

    CREATE OR REPLACE TRIGGER AB_PREINSERT
    BEFORE INSERT ON AB
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    v_count number :=0
    Begin
    v_count := v_count+1;
    :NEW.AB_ID := :NEW.BF_ID||'.'||:NEW.FS_ID||'.'||v_count;
    End;

    but I don't get correct values

    Have you any idea?
    Thanks

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    When does the sequence reset to zero? If never, you can use a SEQUENCE (but will get gaps in the numbers). Other alternatives would be to store the latest value in a table, or for the course of a session you could store the value in a package variable. The v_count variable in your trigger is reset to zero every time the trigger fires.

  8. #8
    Join Date
    Jul 2002
    Posts
    227
    I tried with sequence:

    CREATE SEQUENCE counter
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99999999
    MINVALUE 1;

    CREATE OR REPLACE TRIGGER AB_PREINSERT
    BEFORE INSERT ON AB
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    Begin
    :NEW.AB_ID := :NEW.BF_ID||'.'||:NEW.FS_ID||'.'||counter.nextval;
    End;

    but I get error
    PLS-00357

    How Can I resolve my problem??

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can't reference a sequence in an assignment statement, so you must do this:

    SELECT :NEW.BF_ID||'.'||:NEW.FS_ID||'.'||counter.nextval
    INTO :NEW.AB_ID
    FROM DUAL;

Posting Permissions

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