Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: Help with pl/sql trigger

    I ahve the following trigger

    create or replace trigger trigger_status
    after insert or update on e_ritningar_documents
    referencing new as inserted_row
    for each row
    declare
    drawing_type varchar2(3);

    begin
    drawing_type := substr(:inserted_row.doc_name,1,3);

    IF (drawing_type = '15-') THEN


    UPDATE ib2_drawing_issue@NUVDB.WORLD SET ib2_caradoc_status = :inserted_row.doc_status
    WHERE IB2_PART_NO_1 = substr(:inserted_row.doc_name,1,2) AND
    IB2_PART_NO_2 = substr(:inserted_row.doc_name,4,8) AND
    IB2_PART_NO_3 = substr(:inserted_row.doc_name,12,1) AND
    IB2_DRAWING_ISSUE = substr(:inserted_row.doc_name,14,2);
    --UPDATE TEST1@NUVDB.WORLD SET NAME = substr(:inserted_row.doc_name,14,2);

    ELSE

    UPDATE ib2_drawing_issue@NUVDB.WORLD SET ib2_caradoc_status = :inserted_row.doc_status
    WHERE IB2_PART_NO_1 = 15 AND
    IB2_PART_NO_2 = substr(:inserted_row.doc_name,1,7) AND
    IB2_PART_NO_3 = substr(:inserted_row.doc_name,8,1) AND
    IB2_DRAWING_ISSUE = substr(:inserted_row.doc_name,10,2);

    END IF;
    end;


    The problem is that I get the OLD value in ib2_caradoc_status... it should be the new value!!! Am i doing something wrong with "referencing"???


    thank you
    Henrik

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Am i doing something wrong with "referencing"???
    It doesn't look like it. What happens if you remove the REFERENCING clause and refer to :NEW instead of :INSERTED_ROW in the trigger code? (That would have the advantage of making it even easier to understand too - i.e. one less thing to have to "look up")

Posting Permissions

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