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

Thread: problem with trigger........

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    problem with trigger........

    Hi,
    I've these tables:

    TAB TR:

    PROP_TI..........PROP_TR
    ONE..............ONE_NEW
    TWO..............TWO_NEW
    FOUR.............FOUR_NEW
    FIVE.............FIVE_NEW


    TAB LS_TR

    COD...........PROP..........MY_PROP.......PROP_TI
    001............A............MY_ONE...........ONE
    002............B............MY_TWO...........TWO
    005............A............MY_FOUR...........FOUR
    006............C............MY_FIVE...........FIVE


    I'd like to update the column PROP_TI when I update the col PROP with these conditions:

    If PROP='A' then PROP_TI=LS_TR.MY_PROP
    IF PROP='B' or 'C' then PROP_TI=TR.PROP_TR


    I created this trigger:

    CREATE OR REPLACE TRIGGER upd_prop_tr
    BEFORE UPDATE
    ON ls_tr
    FOR EACH ROW
    DECLARE
    v_prop_tr VARCHAR2 (64);
    BEGIN
    IF :NEW.prop = 'A'
    THEN
    :NEW.prop_ti := :OLD.my_prop;
    ELSIF :NEW.PROP IS NULL THEN
    :NEW.prop_ti := NULL;
    ELSIF :NEW.prop = 'B' OR :NEW.prop = 'C'
    THEN
    SELECT prop_tr
    INTO v_prop_tr
    FROM tr
    WHERE prop_tr = :OLD.prop_ti;

    :NEW.prop_ti := v_prop_tr;
    END IF;
    END;
    /

    I tried this update:

    UPDATE LS_TR
    SET PROP='A'
    WHERE COD='002';

    OK ---> PROP_TI = 'MY_TWO'

    UPDATE LS_TR
    SET PROP=NULL
    WHERE COD='002';

    OK ---> PROP_TI = NULL

    UPDATE LS_TR
    SET PROP='B'
    WHERE COD='002';

    In this case I get error:
    ORA-01403: no data found
    ORA-06512: at "UPD_PROP_TR", line 11
    ORA-04088: error during execution of trigger 'UPD_PROP_TR'

    I think there are any problem on query:
    SELECT prop_tr
    INTO v_prop_tr
    FROM tr
    WHERE prop_tr = :OLD.prop_ti;

    because WHERE prop_tr = :OLD.prop_ti not always valid (no data found)

    I tried also
    SELECT prop_tr
    INTO v_prop_tr
    FROM tr
    WHERE prop_ti = :OLD.prop_ti;

    but I get same error!

    How can I resolve my problem???

    Thank in advance

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Try this:
    Code:
    ...etc...
    ELSIF :NEW.prop = 'B' OR :NEW.prop = 'C'
    THEN
      BEGIN
        SELECT prop_tr
          INTO v_prop_tr
          FROM tr
         WHERE prop_tr = :OLD.prop_ti;
        :NEW.prop_ti := v_prop_tr;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          :NEW.prop_ti := NULL;
      END:
    END IF;


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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