Home » Developer & Programmer » Forms » When-Button-Pressed Trigger Code to cover three scenarios (Oracle Forms 6i)
When-Button-Pressed Trigger Code to cover three scenarios [message #440080] Wed, 20 January 2010 23:16 Go to next message
DBase482
Messages: 6
Registered: January 2010
Junior Member
Hello, I'm writing a when-button-pressed trigger on a save button for an Oracle Forms 6i form, and it has to fulfill a couple of scenarios.

Here's some background information:
the fields we're primarily concerned with are:

n_number,
alert_id,
end_date

For all three scenarios we are comparing candidate records against the following records in the database (for the sake of
argument, let's assume they're the only records in the database
so far):

alert_id|| n_number|| end_date
-------------------------------------
1|| 5|| _______
2|| 6 || 10/25/2009

Scenario 1: The user enters a new record:
alert_id 1
n_number 5
end_date NULL

Objective: prevent the user from committing duplicate rows

Scenario 2: The user enters a new record:

alert_id 1
n_number 10
end_date NULL

Objective: Notify the user that this alert_id already exists, but allow the user the ability to commit the row, if desired.

Scenario 3: The user enters a new record:

alert_id 2
n_number 6
end_date NULL

Objective: Notify the user that this alert_id has occurred in the past (i.e. it has a not-null end_date), but allow the user to commit the row, if desired.

Attached is my code, which seems to comply with the first two scenarios, but prevents me from fulfilling the third.

Issues: When I enter the third scenario case, I am prompted to commit the record, but when I attempt this, the "duplicate_stop" alert pops up, preventing me.

Issues: I'm getting the following error: ORA-01843: not a valid month. While testing the code for the third scenario in Toad (hard-coding the values, etc) things seemed to be fine. Why would I encounter these problems at run-time?

Help is very much appreciated.

Thank you


  • Attachment: code.sql
    (Size: 3.31KB, Downloaded 357 times)
Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440091 is a reply to message #440080] Thu, 21 January 2010 00:46 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What datatatype is ":table_x.end_date"? It *should* be DATE - I hope you aren't storing dates into a character column, are you? If we suppose that it is a DATE after all, then this part
AND (TO_CHAR(end_date, 'MM/DD/YYYY') = :table_x.end_date
should be rewritten as
AND end_date = :table_x.end_date
i.e. no conversion is needed. "Not a valid month" is usually result of wrong explicit conversion (use of TO_CHAR or TO_DATE) or relying on implicit conversion (which is even worse, because what works here doesn't have to work there).

Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440095 is a reply to message #440091] Thu, 21 January 2010 00:56 Go to previous messageGo to next message
DBase482
Messages: 6
Registered: January 2010
Junior Member
In response to your question, :table_x.end_date is stored as DATE,
and the reason for the following condition:

AND (TO_CHAR(end_date, 'MM/DD/YYYY') = :table_x.end_date

has to do with the fact that the record that's being matched from the database with the input record may not have an end_date. By the way, the user cannot enter an end_date. The purpose is to check the input data against existing records that may or may not have an end_date. If the end_date IS NULL, yet the n_number and alert_ids match, then the user is prevented from committing the record. However, if the end_date IS NOT NULL, then the user can have the option of committing the record.

I hope this clarifies some things.

Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440102 is a reply to message #440095] Thu, 21 January 2010 01:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You have put
         WHEN OTHERS THEN
             NULL;  

That's why this error is being handled in other scenarios.

As explained by Littlefoot, use
AND end_date = :table_x.end_date
instead of to_char.Quote:
The purpose is to check the input data against existing records that may or may not have an end_date.
It doesn't matter in whichever format you enter the date, that will be treated as same in database or form.
So, while comparing it should be date -> date or char -> char.
It shouldn't be char -> date or date -> char, which leads to implicit conversion, which is depending on nls_date_format.

Please follow the suggestion and try. Also remove NULL in "WHEN OTHERS".

By
Vamsi

[Updated on: Thu, 21 January 2010 02:44]

Report message to a moderator

Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440218 is a reply to message #440080] Thu, 21 January 2010 12:33 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm confused by something.
If the user can not enter an end_date then presumably :table_x.end_date is always null when users are entering records (and do you need to cover updates to records here or is just inserts?). If that's the case why are you checking it in any of the queries?

Some thoughts on the code:
1) As Vamsi and Littlefoot have pointed out you shouldn't be to_charing the date columns and the exception when others should be removed as they will just hide bugs.
2) I wouldn't put multiple commits in the code, rather set a flag to say if the commit should be done and do it once at the end.
3) A lot of the IF checks are redundant as they must be true if the preceding select found data, and if it didn't then the code will skip straight to the exception handler and bypass the if.
For example this bit:
  BEGIN
  
    SELECT 'X', n_number, alert_id, end_date
    INTO lv_exists, v_n, v_alert_id, v_end_date
    FROM table_x
    WHERE alert_id = :table_x.alert_id
    AND  n_number = :table_x.n_number
    AND (end_date = :table_x.end_date
         OR end_date IS NULL);
      
    IF lv_exists = 'X'
    AND v_n IS NOT NULL
    AND v_alert_id IS NOT NULL
    AND v_end_date IS  NOT NULL THEN
      set_alert_property('PAST_WARNING', alert_message_text,
 'This alert   occurred in the past. do you still want to enter the recall?');
      al_button := show_alert('PAST_WARNING');
      IF al_button = alert_button1 THEN
        COMMIT;
      END IF;       
    END IF;
      
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      COMMIT;

    WHEN OTHERS THEN
      RAISE form_trigger_failure;    
  END;


Is equivalent to:
  BEGIN
  
    SELECT n_number, alert_id, end_date
    INTO v_n, v_alert_id, v_end_date
    FROM table_x
    WHERE alert_id = :table_x.alert_id
    AND  n_number = :table_x.n_number
    AND (end_date = :table_x.end_date
         OR end_date IS NULL);
      
    IF v_end_date IS NOT NULL THEN
      set_alert_property('PAST_WARNING', alert_message_text,
 'This alert   occurred in the past. do you still want to enter the recall?');
      al_button := show_alert('PAST_WARNING');
      IF al_button = alert_button1 THEN
        COMMIT;
      END IF;       
    END IF;
      
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      COMMIT;

    WHEN OTHERS THEN
      RAISE form_trigger_failure;    
  END;

That's based on an assumption from your problem description that both n_number and alert_id are not null columns.

4) I strongly suspect you've got more queries than you need to do the job, this can probably be done in 1 or 2 queries but you'll have to answer my questions above before I can tell for sure.
[EDITED by DJM: split overly long lines]

[Updated on: Wed, 24 February 2010 22:08] by Moderator

Report message to a moderator

Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440223 is a reply to message #440218] Thu, 21 January 2010 12:48 Go to previous message
DBase482
Messages: 6
Registered: January 2010
Junior Member
In response to cookiemonster's questions, so far the form is only covering inserts, and yes, alert_id and n_number are both not null columns.

Now I realize that I've been misguided in checking for :table_x.end_date. However, in scenarios 1 and 2 I want to make sure that I'm comparing the candidate record against pre-existing records that do not have an end_date. In scenario 3 I'm deliberately comparing the candidate record against pre-existing records that DO have a populated end_date. It looks like my code as it is now isn't conveying this intention.

Thank you for your help so far, everyone.
Previous Topic: Can not connect to DB
Next Topic: Validation in forms
Goto Forum:
  


Current Time: Fri Sep 30 05:29:44 CDT 2016

Total time taken to generate the page: 0.21626 seconds