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

    Question Unanswered: Creating a Trigger Using Oracle Enterprise Manage

    I'm looking to create a trigger using the Oracle Enterprise Manager.
    I want a table feild to be updated based on an event from another
    table.

    I have 2 tables (PPLP and PAWU) the first table (PPLP) has the
    following relevent fields "Item_Nr" and "Correction_Date".

    The second table (PAWU) contains "Item_Nr" and "Adjustment_Status".

    I want a trigger to occur when the Correction_Date column is updated
    in table PPLP I want the same associated Item_Nr in table PAWU
    Adjustment_Status to be updated from "00" to "02".

    I've stepped through the Oracle Enterprise Manager's menu for
    creating a trigger but I'm stuck on the advanced tab with the
    referencing fields Old as:, New as: and the Condition feild.

    Here's the SQL statement that I have so far:


    Code:
    CREATE TRIGGER "MOSCA"."PPLP_PAWU_UPDATE" BEFORE
    UPDATE OF "Correction_Date" ON "MOSCA"."PAWU" FOR EACH ROW

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Trigger would be such as this one:
    Code:
    CREATE OR REPLACE TRIGGER trg_upd 
    AFTER UPDATE OF correction_date ON PPLP
    FOR EACH ROW
    BEGIN
      UPDATE PAWU SET
        adjustment_status = DECODE(adjustment_status, '00', '02', adjustment_status)
        WHERE item_nr = :NEW.item_nr;
    END;
    You didn't say what to do with the adjustment_status when its existing value differs from '00', so it is left as it was.

    P.S. Read more about triggers here.
    Last edited by Littlefoot; 01-31-05 at 05:17.

  3. #3
    Join Date
    Nov 2004
    Posts
    57

    Question

    The other possible values for adjustment_status are "04","05" but those should be left as they are if they are already present. Would the code need to be changed to leave these values as they are?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No; DECODE takes care of it - if adjustment_status = '00' then change it to '02'; otherwise, don't change it.

Posting Permissions

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