Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Unhappy Unanswered: WHEN-VALIDATE-ITEM & KEY-NEXT-ITEM Issues. Please help

    Hi guys

    I am fresh into the field of Oracle programming & I finding troubles with understanding the trigger KEY-NEXT-ITEM.

    For example I added a WHEN-VALIDATE-ITEM & KEY-NEXT-ITEM with a particular item of my table. WHEN-VALIDATE-ITEM checks for a wrong date entered & to my best knowledge incase if a RAISE FORM TRIGGER FAILURE occurs the form MUST prevent other codes attached with the items from firing. In my case instead of halting the triggers from firing, KEY-NEXT-ITEM trigger fires and literally empties my data block. I know it is because the next key button is pressed (ENTER or TAB key).

    Please tell me is there any other triggers I can use instead of KEY-NEXT-ITEM & halt the codes from firing immediately after a WHEN-VALIDATE-ITEM trigger fails the rules?

    I am pasting the codes I written for both WHEN-VALIDATE-ITEM & KEY-NEXT-ITEM triggers here. Please advice me.

    Trigger name: WHEN-VALIDATE-ITEM

    if :Prj_dtls.e_out_dt < :Prj_dtls.e_in_dt or
    :Prj_dtls.e_out_dt > :Prj_hdr.prj_edt then
    message('Invalid date value. Please correct your entry.');
    raise form_trigger_failure;
    end if;

    followed by

    Trigger name: KEY-NEXT-ITEM

    DECLARE
    D1 DATE;
    D2 DATE;
    T_DAYS NUMBER:=0;
    T_FRIDAYS NUMBER:=0;
    CC NUMBER:=0;
    it_mask varchar2(80);
    BEGIN
    D1:=:PRJ_DTLS.E_IN_DT;
    D2:=:PRJ_DTLS.E_OUT_DT;
    T_DAYS:=(D2-D1)+1;
    FOR CC IN 1..T_DAYS LOOP
    IF TO_CHAR(D1,'DAY') LIKE '%FRIDAY%' THEN
    T_FRIDAYS:=T_FRIDAYS+1;
    END IF;
    D1:=D1+1;
    END LOOP;
    :PRJ_DTLS.E_TOT_DAYS:=(T_DAYS-T_FRIDAYS);
    CHECK_CONFLICT;
    next_record;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
    END;
    NEXT_ITEM;

    Anybody thinks I must not use KEY-NEXT-ITEM immediately after a WHEN-VALIDATE-TRIGGER, if so which trigger will solve my problem. Kindly advice.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The KEY-NEXT-ITEM trigger fires before the WHEN-VALIDATE-ITEM trigger. Try adding this code at the very top of the KEY-NEXT-ITEM trigger:
    Code:
    validate(item_scope);
    if form_failure then 
      raise form_trigger_failure; 
    end if;
    That will force the validation to take place before the KEY-NEXT-ITEM trigger does anything else - and will abort the trigger if there is an error.

  3. #3
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Smile It does work, still I have problems...

    Quote Originally Posted by andrewst
    The KEY-NEXT-ITEM trigger fires before the WHEN-VALIDATE-ITEM trigger. Try adding this code at the very top of the KEY-NEXT-ITEM trigger:
    Code:
    validate(item_scope);
    if form_failure then 
      raise form_trigger_failure; 
    end if;
    That will force the validation to take place before the KEY-NEXT-ITEM trigger does anything else - and will abort the trigger if there is an error.
    First of all let me thank you Tony. You tip works fine, but instead of a single form_trigger_failure warning, now I am experiencing minimum three warnings for a single row data mismatch/format mismatch, because I had a ON-ERROR trigger attached to that same block item to check whether the date format specified (dd.mm.yy) fails. Is it possible for me to trap the error types and error codes within the KEY-NEXT-ITEM trigger? Please be kind enough to guide me incase if error trapping is possible within the KEY-NEXT-ITEM.

    Thank you
    If it is a door, It will open.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think if you are getting 3 warnings and only want 1 then the answer is to raise FORM_TRIGGER_FAILURE after displaying the first warning.

  5. #5
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    38

    Thumbs up :d

    Quote Originally Posted by andrewst
    I think if you are getting 3 warnings and only want 1 then the answer is to raise FORM_TRIGGER_FAILURE after displaying the first warning.
    Dear Tony

    Today by accidently I found out something. Once we attach a KEY-NEXT-ITEM trigger with a date column and any errors like an invalid date is fed into the form or a date is entered in a wrong format, the form error (50012) fires, but KEY-NEXT-ITEM trigger proceeds to execute the scripts included within the body of it, with the form fowarded null value for the date colurm. It was the reason for those multiple warnings & rest of the issues.

    Once I figured out form passes "null" once after formate failure happens, I just wrote the following line immediately after the BEGIN statement

    BEGIN
    if :blockname.datecolumn is null
    message('Invalid Date/format');
    raise form_trigger_failure;
    end if;

    /*Rest of the script*/
    ---
    ---
    /*-----*/
    This solved my nightmare, and everything works fine now.

    Thanks again
    If it is a door, It will open.

Posting Permissions

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