DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: error trigger......

  1. #1
    Join Date
    Jul 2002
    Posts
    228
    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',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
    IF COD2 = 'Z' THEN
    COD2 := '0';
    COD3 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,3,1)) 1,1);
    ELSE
    COD2 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('0123456789ABCDEFGHIJKLMNO PQRSTUVWYXZ0',SUBSTR(COD_SITO,2,1)) 1,1);
    END IF;
    ELSE
    COD1 := SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWYXZ0',INSTR('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
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492



    Better try the following:

    1) Create a sequence:

    Create sequence my_seq
    minvalue 0 start with 0;

    2) Create the following function:

    create or replace
    function alpha_seq(sq number)
    return varchar2 is
    a Varchar2(40):='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    l pls_integer;
    s1 pls_integer;
    d1 pls_integer:=0;
    d2 pls_integer:=0;
    d3 pls_integer:=0;
    i1 pls_integer:=0;
    i2 pls_integer:=0;
    i3 pls_integer:=0;
    Begin
    l:=length(a);
    s1:=sq;
    i1:=trunc(s1/l);
    d1:=mod(s1,l);
    if i1 > 0 then
    s1:=s1-i1*l;
    i2:=trunc(s1/l);
    d2:=mod(s1,l);
    if i2 > 0 then
    s1:=s1-i2*l;
    i1:=trunc(s1/l);
    d3:=mod(s1,l);
    end if;
    end if;
    return '_'||substr(a,d3+1,1)
    ||substr(a,d2+1,1)
    ||substr(a,d1+1,1);
    end;
    /

    3) Create the trigger:

    Create Or Replace Trigger On_Site_Id
    Before Insert Or Update Of Site_Id
    On Bl For Each Row
    Declare
    Sq Number;
    Begin
    Select My_Seq.Nextval Into Sq From Dual;
    :New.Cod_Conv := Alpha_Seq(Sq);
    End;

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    ok,
    but the result is
    SITE_ID COD_CONV
    -------------------------------- ---------------
    03022038 _000
    05035238 _002
    02014837 _007
    12034041 ____-_-________
    02607812 _009
    07601955 ____-_-________
    02050658 _00D
    02034645 ____-_-________
    02607796 ____-_-________

    not
    _000
    _001
    _002
    ...
    ...

    and after how can I insert the max value into another table MAX_VAL???

    Thanks
    Raf

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    Don't know what you did but it works for me:

    SQL>Create sequence my_seq
    2 minvalue 0 start with 0;

    Sequence created.

    SQL>create or replace
    2 function alpha_seq(sq number)
    3 return varchar2 is
    4 a Varchar2(40):='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    5 l pls_integer;
    6 s1 pls_integer;
    7 d1 pls_integer:=0;
    8 d2 pls_integer:=0;
    9 d3 pls_integer:=0;
    10 i1 pls_integer:=0;
    11 i2 pls_integer:=0;
    12 i3 pls_integer:=0;
    13 Begin
    14 l:=length(a);
    15 s1:=sq;
    16 i1:=trunc(s1/l);
    17 d1:=mod(s1,l);
    18 if i1 > 0 then
    19 s1:=s1-i1*l;
    20 i2:=trunc(s1/l);
    21 d2:=mod(s1,l);
    22 if i2 > 0 then
    23 s1:=s1-i2*l;
    24 i1:=trunc(s1/l);
    25 d3:=mod(s1,l);
    26 end if;
    27 end if;
    28 return '_'||substr(a,d3+1,1)
    29 ||substr(a,d2+1,1)
    30 ||substr(a,d1+1,1);
    31 end;
    32 /

    Function created.

    SQL>create table bl
    2 (SITE_ID varchar2(10), COD_CONV varchar2(10))
    3 /

    Table created.

    SQL>Create Or Replace Trigger On_Site_Id
    2 Before Insert Or Update Of Site_Id
    3 On Bl For Each Row
    4 Declare
    5 Sq Number;
    6 Begin
    7 Select My_Seq.Nextval Into Sq From Dual;
    8 :New.Cod_Conv := Alpha_Seq(Sq);
    9 End;
    10 /

    Trigger created.

    SQL>select * from bl;

    no rows selected

    SQL>insert into bl values('03022038','___-_-___');

    1 row created.

    SQL>insert into bl values('05035238','___-_-___');

    1 row created.

    SQL>insert into bl values('02014837','___-_-___');

    1 row created.

    SQL>insert into bl values('12034041','___-_-___');

    1 row created.

    SQL>insert into bl values('02607812','___-_-___');

    1 row created.

    SQL>insert into bl values('07601955','___-_-___');

    1 row created.

    SQL>insert into bl values('02050658','___-_-___');

    1 row created.

    SQL>insert into bl values('02034645','___-_-___');

    1 row created.

    SQL>insert into bl values('02607796','___-_-___');

    1 row created.

    SQL>select * from bl;

    SITE_ID COD_CONV
    ---------- ----------
    03022038 _000
    05035238 _001
    02014837 _002
    12034041 _003
    02607812 _004
    07601955 _005
    02050658 _006
    02034645 _007
    02607796 _008

    9 rows selected.

    To update the max_value just add the following to the trigger:

    UPDATE MAX_VAL SET COD_CONV = :NEW.COD_CONV;
    Like:
    Create Or Replace Trigger On_Site_Id
    Before Insert Or Update Of Site_Id
    On Bl For Each Row
    Declare
    Sq Number;
    Begin
    Select My_Seq.Nextval Into Sq From Dual;
    :New.Cod_Conv := Alpha_Seq(Sq);
    UPDATE MAX_VAL SET COD_CONV = :NEW.COD_CONV;
    End;

    PS: NO Need to create an 'AFTER' trigger!.

Posting Permissions

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


Click Here to Expand Forum to Full Width