Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Trigger in 8.1.7

    I'm new to creating triggers in Oracle, having more experience in SQL Server. I am having difficulty getting this to compile, receiving a variety of error messages no matter how I move lines of code around. I am trying to insert a varchar2 value into column CM_ID_ALPHA, and pad it with zeroes, based on the value in numeric column CM_ID. Here's the code. I would appreciate it if someone can spot what's wrong, remembering this is 8.1.7. I originally used CM_ID in the compare statement, but the compiler tells me it's not valid. I try to set cmid to the NEXTVAL of a sequence, but the compiler says I can't access NEXTVAL in this context.

    CREATE OR REPLACE TRIGGER MTRAN.CM_HEADER_I_TR
    AFTER INSERT
    ON MTRAN.CM_HEADER

    DECLARE cmid NUMBER;

    BEGIN
    IF (cmid < 10) THEN
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-00000'||CM_ID
    WHERE CM_ID = cmid;

    ELSIF (cmid >= 10 AND cmid < 100) THEN
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-0000'||CM_ID
    WHERE CM_ID = cmid;

    ELSIF (cmid >= 100 AND cmid < 1000) THEN
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-000'||CM_ID
    WHERE CM_ID = cmid;

    ELSIF (cmid >= 1000 AND cmid < 10000) THEN
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-00'||CM_ID
    WHERE CM_ID = cmid;

    ELSIF (cmid >= 10000 AND cmid < 100000) THEN
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-0'||CM_ID
    WHERE CM_ID = cmid;

    ELSE
    update CM_HEADER
    set CM_ID_ALPHA = 'CM-'||CM_ID
    WHERE CM_ID = cmid;
    END IF;
    END;

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    how about something like this?
    PHP Code:
    create or replace trigger mtran.cm_header_i_tr
        BEFORE insert on mtran
    .cm_header 
        
    for each row

        
    declare 
        
    begin

            
    new.cm_id_alpha :=
            CASE 
                
    when new.cmid 10 then 'CM-00000'||new.cm_id
                when 
    (new.cmid >= 10 AND new.cmid 100then 'CM-0000'||new.cm_id
                when 
    (new.cmid >= 100 AND new.cmid 1000then 'CM-000'||new.cm_id
                when 
    (new.cmid >= 1000 AND new.cmid 10000then 'CM-00'||new.cm_id
                when 
    (new.cmid >= 10000 AND new.cmid 100000then 'CM-0'||new.cm_id
                
    else 'CM-'||new.cm_id
            END
    ;
        
    end
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Code:
    CREATE OR REPLACE TRIGGER MTRAN.CM_HEADER_I_TR
       BEFORE INSERT
       ON MTRAN.CM_HEADER
    BEGIN
       :NEW.CM_ID_ALPHA := 'CM-'||LPAD(CM_ID,6,'0');
    END;
    And better to use the sequence if you can.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Nov 2004
    Posts
    4

    Error messag

    I had tried using NEW. I get this error message.

    ORA-04082: NEW or OLD references not allowed in table level triggers

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Correct, You can't do a statement level trigger, you have to do a row level trigger using for "each row "

    Also make the trigger before update, not after. You want to change the value on insert, not after it has been written.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Oops, my mistake. That code should be...

    Code:
    CREATE OR REPLACE TRIGGER MTRAN.CM_HEADER_I_TR
       BEFORE INSERT
       ON MTRAN.CM_HEADER
       FOR EACH ROW
    BEGIN
       :NEW.CM_ID_ALPHA := 'CM-'||LPAD(:NEW.CM_ID,6,'0');
    END;
    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Nov 2004
    Posts
    4

    Thanks

    Ahhhh I see

    Thanks Bill and Duck

Posting Permissions

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