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  |
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 1392 times)
|
|
|
|
|
|
| Re: When-Button-Pressed Trigger Code to cover three scenarios [message #440095 is a reply to message #440091] |
Thu, 21 January 2010 00:56  |
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 |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
You have put
That's why this error is being handled in other scenarios.
As explained by Littlefoot, useAND end_date = :table_x.end_date 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 |
cookiemonster
Messages: 13938
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 05:00:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
