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

    Unanswered: error trigger......

    hi,
    I've 2 columns SITE_ID and COD_CONV (default '____-_-________' varchar2(15)).
    When I update SITE_ID I must insert automatically into col COD_CONV this value progressive:
    _000
    _001
    _002
    ....
    _A00
    _A01
    ....
    _ZZZ

    after I must fill up a table with max value update.

    I tried with this two trigger:

    create or replace TRIGGER ON_SITE_ID
    BEFORE INSERT OR UPDATE OF
    SITE_ID
    ON BL
    REFERENCING
    NEW AS NEW
    OLD AS OLD
    FOR EACH ROW
    DECLARE COD_SITO VARCHAR2(4);
    COD1 CHAR(1);
    COD2 CHAR(1);
    COD3 CHAR(1);

    Begin
    SELECT COD_CONV INTO COD_SITO FROM MAX_VAL;
    COD1 := SUBSTR(COD_SITO,4,1);
    COD2 := SUBSTR(COD_SITO,3,1);
    COD3 := SUBSTR(COD_SITO,2,1);
    IF COD3='_' THEN COD3:= '0';
    END IF;
    IF COD2='_' THEN COD2:= '0';
    END IF;
    IF COD1='_' THEN COD1:= '0';
    END IF;
    IF COD1='Z' THEN
    COD1 := '0';
    COD2 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INS TR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
    IF COD2 = 'Z' THEN
    COD2 := '0';
    COD3 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INS TR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,3,1)) 1,1);
    ELSE
    COD2 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INS TR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
    END IF;
    ELSE
    COD1 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INS TR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,1,1)) 1,1);
    END IF;
    SELECT '_'||COD3||COD2||COD1||'-' INTO :NEW.COD_CONV FROM DUAL;
    End;




    create or replace TRIGGER POST_COD_CONV
    AFTER INSERT OR UPDATE OF
    COD_CONV
    ON BL
    FOR EACH ROW
    Begin
    UPDATE MAX_VAL
    SET COD_CONV = :NEW.COD_CONV;

    End;


    but when I update SITE_ID, I see only _000 in COD_CONV
    What I wrong???
    Thanks
    Raf

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

    Re: error trigger......

    Have a look at this:

    http://pipetalk.quest-pipelines.com/read?6722,40

    It gives a better method of converting a decimal number to base 36 (i.e. combination of 0-9A-Z characters).

Posting Permissions

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