IndahnyaBerbagi: How To Stop Cursor Leaving A Record/Block In Forms When Data Has Been Changed

How To Stop Cursor Leaving A Record/Block In Forms When Data Has Been Changed

Posted in | at Thursday, November 13, 2008

Applies to:

Oracle Forms - Version: 4.5 to 10.1

Information in this document applies to any platform.

Checked for relevance 17-APR-2008

Goal

A Master-Detail form has to adher to the following business rules once data in a block or record has been changed.

When an
attempt is made to leave a record, and the record status is changed or new, the form should display the 'Do you want to save the
changes' dialogue.

When an attempt is made to leave a BLOCK, and the record status is changed or new, the form should display
the 'Do you want to save the changes' dialogue.

In both cases, if the user clicks Yes in the 'Do you want to save changes'
dialogue, the changes will be saved, if the user clicks No then the focus should stay in the current record (i.e no navigation should take place when user chooses not to save changes)

Solution

The business requirements appear to be straight-forward but it is not easy to implement in forms
due to the fact that COMMIT_FORM built-in is restricted e.g coding a POST-RECORD trigger as follows:

Code:

if :SYSTEM.RECORD_STATUS in ( 'CHANGED', 'INSERT' ) then
if Disp.YesNo ( 'Save ?' ) then
commit_form;
else
raise form_trigger_failure;
end if;
end if;

This does not work because COMMIT_FORM is a restricted built-in and cannot be called in a
POST-RECORD trigger.

A solution is to use a timer so the commit_form is executed outside of triggers like WHEN-VALIDATE-RECORD, POST-RECORD in which the built-in is restricted.

Example Code as follows (This simple example works in a Master Detail form based on demo tables DEPT and EMP)

1. Create
an alert called MYCOMMIT
Properties:

Message: Do you want to save the changes?
Alert Style: Stop
Button 1 Label : Yes
Button 2 Label: No

2. Code a WHEN-VALIDATE-RECORD trigger at form level

Code:

DECLARE
timer_id Timer;
/* set timer to a very lower value - number is in milliseconds */
timer_period NUMBER(5) := 60;
alert_button NUMBER;
BEGIN
IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
/* Display custom alert */
alert_button := SHOW_ALERT('mycommit');
IF alert_button = ALERT_BUTTON1 THEN
/* User has clicked 'Yes' to save changes, start timer when timer expires the commit_form will
execute outside of the WHEN-VALIDATE-RECORD trigger in the WHEN-TIMER-EXPIRED trigger */
timer_id := CREATE_TIMER('emp_timer', timer_period, NO_REPEAT);
END IF;
IF alert_button = ALERT_BUTTON2 THEN
/* User has clicked 'No', therefore raise form trigger failure so cursor remains in the current
record / item */
RAISE FORM_TRIGGER_FAILURE;
END IF;
END IF;
END;