ORACLE FORMS PART 2
7. When-Radio-Changed
Description
A fire when an operator selects a different radio button in a radio group, or de-selects the currently selected radio button, either by clicking with the mouse, or using the keyboard.
Initiate an action when an operator changes the current radio button selected in a radio group item.
Usage Notes
· Use a When-Radio-Changed trigger to perform an action depending on the state of a radio group. (De-selecting a radio button in a radio group sets the radio group value to NULL; operators use this technique in Enter Query mode to exclude a radio group from a query.)
· When an operator clicks an item in a radio group, the internal value of that item does not change until navigation is completed successfully. Thus, the When-Radio-Changed trigger is the first trigger to register the changed value of a radio group. For all navigation triggers that fire before the When-Radio-Changed trigger, the value of the radio group remains as it was before the operator navigated to it.
When-Radio-Changed Trigger examples
When the user selects credit as the payment type for an order, this trigger immediately confirms whether the customer has a good or excellent credit rating. If not, then the payment type is set to cash.
Declare
v_credit customer.credit_rate%type;
Begin
If :s_ord.payment_type=’CREDIT’ then
Select credit_rate into v_credit from customer where custid=:s_ord.custid;
If v_credit NOT_IN(‘GOOD’,’EXCELLENT’) then
:s_ord.payment_type:=’CASH’;
message(‘Warning – Customer must pay cash‘);
End if;
End if;
End;
8. When-Timer-Expired
Description
Fires when a timer expires.
Initiate an action when a programmatic timer expires.
Usage Notes
Timers are created programmatically by calling the CREATE_TIMER built-in procedure.
· The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction processing.
· Use a When-Timer-Expired trigger to initiate an event, update item values, or perform any task that should occur after a specified interval.
· You can call GET_APPLICATION_PROPERTY(TIMER_NAME) in a When-Timer-Expired trigger to determine the name of the most recently expired timer.
Fires In
· Process Expired Timer
When-Timer-Expired Trigger examples
Example
The following example displays a message box each time a repeating timer expires. The following example is from a telemarketing application, in which sales calls are timed, and message boxes are displayed to prompt the salesperson through each stage of the call. The message box is displayed each time a repeating timer expires.
DECLARE
timer_id TIMER;
alert_id ALERT;
call_status NUMBER;
msg_1 VARCHAR2(80) := 'Wrap up the first phase of your presentation';
msg_2 VARCHAR2(80) := 'Move into your close.';
msg_3 VARCHAR2(80) := 'Ask for the order or repeat the close.'
two_minutes NUMBER(6) := (120 * 1000);
one_and_half NUMBER(5) := (90 * 1000);
BEGIN
:GLOBAL.timer_count := 1
BEGIN
timer_id := FIND_TIMER('tele_timer');
alert_id := FIND_ALERT('tele_alert');
IF :GLOBAL.timer_count = 1 THEN
Set_Alert_Property(alert_id, ALERT_MESSAGE_TEXT, msg_1);
call_status := Show_Alert(alert_id);
IF call_status = ALERT_BUTTON1 THEN
Delete_Timer(timer_id);
Next_Record;
ELSIF call_status = ALERT_BUTTON2 THEN
:GLOBAL.timer_count := 0;
ELSE
Set_Timer(timer_id, two_minutes, NO_CHANGE);
END IF;
ELSIF :GLOBAL.timer_count = 2 THEN
Change_Alert_Message(alert_id, msg_2);
call_status := Show_Alert(alert_id);
IF call_status = ALERT_BUTTON1 THEN
Delete_Timer(timer_id);
Next_Record;
ELSIF
call_status = ALERT_BUTTON2 THEN
:GLOBAL.timer_count := 0;
ELSE
Set_Timer(timer_id, one_and_half, NO_CHANGE);
END IF;
ELSE
Change_Alert_Message(alert_id, msg_3);
call_status := Show_Alert(alert_id);
IF call_status = ALERT_BUTTON1 THEN
Delete_Timer(timer_id);
Next_Record;
ELSIF call_status = ALERT_BUTTON2 THEN
:GLOBAL.timer_count := 0;
ELSE
Set_Timer(timer_id, NO_CHANGE, NO_REPEAT);
END IF;
END IF;
:GLOBAL.timer_count = 2;
END;
9. When-Window-Activated
Initiate an action whenever an operator or the application activates a window.
Fires when a window is made the active window. This occurs at form startup and whenever a different window is given focus.
Note that on some window managers, a window can be activated by, say, clicking on its title bar. This operation is independent of navigation to an item in the window. Thus, navigating to an item in a different window always activates that window, but window activation can also occur independently of navigation.
Usage Notes:
Use this trigger to perform the following types of tasks:
· Capture initial settings of window properties, by way of the GET_WINDOW_PROPERTY built–in.
· Enforce navigation to a particular item whenever a window is activated.
· Keep track of the most recently fired window trigger by assigning the value from SYSTEM.EVENT_WINDOW to a variable or global variable.
Example:
Begin
GET_WINDOW_PROPERTY( window_name, property);
End;
10 .When-Window-Closed
Initiate an action whenever an operator closes a window with the window manager's Close command.
Usage Notes:
· Use this trigger to programmatically close a window when the operator issues the window–manager Close command.
· You can close a window with the HIDE_WINDOW , SET_WINDOW_PROPERTY, and EXIT_FORM built–in subprograms.
· You can hide the window that contains the current item.
Example:
The following example of a call to SET_WINDOW_PROPERTY from this trigger closes a window whenever the operator closes it by way of the window manager operation:
Set_Window_Property(’window_name’, VISIBLE, PROPERTY_OFF);
11. When-Window-Deactivated
Initiate an action whenever a window is deactivated as a result of another window becoming the active window.
Fires when an operator deactivates a window by setting the input focus to another window.
Usage Notes:
Use this trigger to audit the state of a window whenever the operator deactivates the window by setting the input focus in another window.
12. When-Window-Resized
Initiate an action whenever a window is resized, either by the operator or programmatically.
Fires when a window is resized, either by the operator or programmatically through a call to RESIZE_WINDOW or SET_WINDOW_PROPERTY. (Even if the window is not currently
displayed, resizing the window programmatically fires the When–Window–Resized trigger.) This trigger also fires at form startup, when the root window is first drawn. It does not fire when a window is iconified.
Usage Notes:
Use this trigger to perform any one of the following types of tasks:
· Capture the changed window properties, such as width, height, x coordinate, or y coordinate.
· Audit the actions of an operator.
· Set input focus in an item on the target window.
· Maintain certain visual standards by resetting window size if the window was improperly resized.
Example:
/* ** Built–in: RESIZE_WINDOW
** Example: Set Window2 to be the same size as Window1 */
PROCEDURE Make_Same_Size_Win( Window1 VARCHAR2, Window2 VARCHAR2)
IS
wn_id1 Window;
w NUMBER;
h NUMBER;
BEGIN
/* ** Find Window1 and get it’s width and height. */
wn_id1 := Find_Window(Window1);
w := Get_Window_Property(wn_id1,WIDTH);
h := Get_Window_Property(wn_id1,HEIGHT);
/* ** Resize Window2 to the same size */
Resize_Window( Window2, w, h );
END;
C. KEY- [ALL] ( KEY TRIGGERS )
Key Triggers have a one-to-one relationship with specific keys.
i.e, the trigger fires when operator presses a specific key or key-sequence.
Replace the default function associated with a function key. For example, you can define a Key-EXIT trigger to replace the default functionality of the [Help] key.
Key Triggers and Function Keys
KEY TRIGGER ASSOCIATED FUNCTION KEY
Key–CLRBLK [Clear Block]
Key–CLRFRM [Clear Form]
Key–CLRREC [Clear Record]
Key–COMMIT [Accept]
Key–CQUERY [Count Query Hits]
Key–CREREC [Insert Record]
Key–DELREC [Delete Record]
Key–DOWN [Down]
Key–DUP–ITEM [Duplicate Item]
Key–DUPREC [Duplicate Record]
Key–EDIT [Edit]
Key–ENTQRY [Enter Query]
Key–EXEQRY [Execute Query]
Key–EXIT [Exit]
Key–HELP [Help]
Key–LISTVAL [List of Values]
Key–MENU [Block Menu]
Key–NXTBLK [Next Block]
Key–NXT–ITEM [Next Item]
Key–NXTKEY [Next Primary Key]
Key–NXTREC [Next Record]
Key–NXTSET [Next Set of Records]
Key–PRINT [Print]
Key–PRVBLK [Previous Block]
Key–PRV–ITEM [Previous Item]
Key–PRVREC [Previous Record]
Key–SCRDOWN [Scroll Down]
Key–SCRUP [Scroll Up]
Key–UP [Up]
Key–UPDREC Equivalent to Record, Lock command on the default menu
1.Key–Fn Trigger
A Key–Fn trigger fires when an operator presses the associated key.
You can attach Key–Fn triggers to 10 keys or key sequences that normally do not perform any Oracle Forms operations. These keys are referred to as Key–F0 through Key–F9. Before you can attach key triggers to these keys, you or the DBA must use Oracle Terminal to map the keys to the appropriate functions.
Usage Notes:
Use Key–Fn triggers to create additional function keys for custom functions.
2. Key–Others Trigger
A Key–Others trigger fires when an operator presses the associated key.
A Key–Others trigger is associated with all keys that can have key triggers associated with them but are not currently defined by function key triggers (at any level).
A Key–Others trigger overrides the default behavior of a Runform function key (unless one of the following restrictions apply). When this occurs, however, Oracle Forms still displays the function key’s default entry in the Show Keys screen.
Usage Notes:
Use Key–Others triggers to limit an operator’s possible actions.
Specifically, use Key–Others triggers to perform the following tasks:
· Disable all keys that are not relevant in a particular situation.
· Perform one specific action whenever an operator presses any key.
KEY-COMMIT TRIGGER AT FORM LEVEL: EMPLOYEES FORM
BEGIN
IF GET_APPLICATION_PROPERTY( CALLING_FORM ) IS NULL THEN
COMMIT_FORM;
ELSE
POST;
END IF;
END;
KEY-DELREC TRIGGER ON S_CUSTOMER BLOCK
DELETE_RECORD;
KEY-EXIT AT FORM LEVEL
SET_ALERT_PROPERTY
(’question_alert’, ALERT_MESSAGE_TEXT, ’Do you really want to leave the form?’);
IF SHOW_ALERT (’question_alert’) = ALERT_BUTTON1 THEN
EXIT_FORM;
END IF;
D.MASTER-DETAIL TRIGGERS
Form Builder generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block.
1. On-Check-Delete-Master
Fires when Form Builder attempts to delete a record in a block that is a master block in a master/detail relation.
DECLARE
the_sum NUMBER;
BEGIN
SELECT SUM(dollar_amt) INTO the_sum FROM po_distribution WHERE po_number = :purchase_order.number;
IF the_sum <> :purchase_order.total THEN
Message(’PO Distributions do not reconcile.’);
RAISE Form_Trigger_Failure;
END IF;
END;
2. On-Clear-Details
Fires when Form Builder needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
Usage Notes:
Oracle Forms creates the On–Clear–Details trigger automatically when you define a master–detail block relation.
3. On-Populate-Details
Fires when Form Builder needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.
Usage Notes:
• Use an On–Populate–Details trigger when you have established a master–detail relationship and you want to replace the default populate phase of a query.
• When Immediate coordination is set, this causes the details of the instantiated master to be populated immediately. Immediate coordination is the default.
• When Deferred coordination is set and this trigger fires, Oracle Forms marks the blocks as needing to be coordinated.
• If you intend to manage block coordination yourself, you can call the
SET_BLOCK_PROPERTY(COORDINATION_STATUS) built–in.
E.MESSAGE-HANDLING TRIGGERS
Form Builder automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
1. On-Error
Replace a default error message with a custom error message, or to trap and recover from an error.
Usage Notes
Use an On–Error trigger for the following purposes:
· To trap and recover from an error
· To replace a standard error message with a custom message Use the ERROR_CODE, ERROR_TEXT, ERROR_TYPE, DBMS_ERROR_TEXT, or DBMS_ERROR_CODE built–in function in an On–Error trigger to identify a specific error condition.
· In most cases, On–Error triggers should be attached to the form, rather than to a block or item. Trapping certain errors at the block or item level can be difficult if these errors occur while Oracle Forms is performing internal navigation, such as during a Commit process.
Example:
The following example checks specific error message codes and responds appropriately.
DECLARE
lv_errcod NUMBER := ERROR_CODE;
lv_errtyp VARCHAR2(3) := ERROR_TYPE;
lv_errtxt VARCHAR2(80) := ERROR_TEXT;
BEGIN
IF (lv_errcod = 40nnn) THEN
/*
** Perform some tasks here
*/
ELSIF (lv_errcod = 40mmm) THEN
/* ** More tasks here */
...
ELSIF (lv_errcod = 40zzz) THEN
** More tasks here
*/
ELSE
Message(lv_errtyp||’–’||to_char(lv_errcod)||’: ’||lv_errtxt);
RAISE Form_Trigger_Failure;
END IF;
END;
2. On-Message
To trap and respond to a message; for example, to replace a default message issued by Form Builder with a custom message.
Usage Notes:
Use an On–Message trigger for the following purposes:
· To trap and respond to an informative message
· To replace a standard informative message with a custom message
· To exclude an inappropriate message
Example:
The following example responds to an error message by displaying an alert that gives the user a message and gives the user the choice to continue or to stop:
DECLARE
alert_button NUMBER;
lv_errtype VARCHAR2(3) := MESSAGE_TYPE;
lv_errcod NUMBER := MESSAGE_CODE;
lv_errtxt VARCHAR2(80) := MESSAGE_TEXT;
BEGIN
IF lv_errcod = 40350 THEN
alert_button := Show_Alert(’continue_alert’);
IF alert_button = ALERT_BUTTON1 THEN
...
ELSE
...
END IF;
ELSE
Message(lv_errtyp||’–’||to_char(lv_errcod)||’: ’||lv_errtxt);
RAISE Form_Trigger_Failure;
END IF;
END;
F.QUERY-TIME TRIGGERS
Query-time triggers fire just before and just after the operator or the application executes a query in a block.
1. Pre-Query
Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
This Pre-Query trigger on the S_ORD block only permits queries if there is a restriction on either the Order ID, Date Ordered, or Date Shipped. This prevents attempts at very large queries.
• To test the operator’s query conditions, and to fail the query process if the conditions are not
satisfactory for the application
• To add criteria for the query by assigning values to base table items
· A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions.
Make sure the user has given one of the two Columns which we have indexed in their search criteria, otherwise fail the query with a helpful message
A] IF :Employee.Ename IS NULL AND :Employee.Mgr IS NULL THEN
Message(’Supply Employee Name and/or Manager Id ’||’for Query.’);
RAISE Form_Trigger_Failure;
END IF;
B] [ exact_match – Check Box
User can specify if or not a query condition for a customer name should exactly match the
table value. [ Set the initial value property to “Y”. ]
IF nvl(:control.exact_match,’Y’)=’N’ then
:S_Customer.name:=’%’||:S_customer.name || ‘%’;
END IF;
2. Post-Query
Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.
This trigger is defined at block level or above. Post-Query fires for each record that is fetched into the block as a result of a query. Note that the trigger only fires on the initial fetch of a record not when a record is subsequently scrolled back into view a second or third time.
Use Post-Query as follows:
· To populate non database items as records are returned from a query
· To calculate statistics
· A Post-Query trigger fires as each record is fetched (except array processing). Use it to
perform calculations and populate additional items.
This Post-Query trigger on the S_ORD block selects the total count of line items for the current Order, and displays this number as a summary value in the non base table item :Lineitem_count.
Begin
SELECT COUNT(ord_id) INTO :S_ORD.lineitem_count FROM S_ITEM WHERE ord_id = :S_ORD.id;
End;
Example:
This example retrieves descriptions for code fields, for display in non–database items in the current block.
DECLARE
CURSOR lookup_payplan IS SELECT Payplan_Desc FROM Payplan WHERE Payplan_Id = :Employee.Payplan_Id;
CURSOR lookup_area IS SELECT Area_Name FROM Zip_Code WHERE Zip = :Employee.Zip;
BEGIN
/* Lookup the Payment Plan Description given the Payplan_Id in the Employee Record just fetched.
** Use Explicit Cursor for highest efficiency.*/
OPEN lookup_payplan;
FETCH lookup_payplan INTO :Employee.Payplan_Desc_Nondb;
CLOSE lookup_payplan;
/* ** Lookup Area Descript given the Zipcode in ** the Employee Record just fetched. Use Explicit
** Cursor for highest efficiency. */
OPEN lookup_area;
FETCH lookup_area INTO :Employee.Area_Desc_Nondb;
CLOSE lookup_area;
END;
A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed?
PRE-QUERY fires once.
POST-QUERY fires 10 times.
· A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions.
· A Post-Query trigger fires as each record is fetched (except array processing). Use it to perform calculations and populate additional items.
What is a difference between pre-select and pre-query?
Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued.
The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.
Pre-query trigger fires before pre-select trigger.
G.NAVIGATIONAL TRIGGERS
Navigational triggers fire in response to navigational events. For instance, when the operator clicks on a text item in another block, navigational events occur as Form Builder moves the input focus from the current item to the target item.
I ] PRE- AND POST- TRIGGERS
Fire as Form Builder navigates internally through different levels of the object hierarchy.
When Do Pre- and Post-Navigation Triggers Fire?
The Pre- and Post- navigation triggers fire during navigation, that is just before entry
to or just after exit from the object specified as part of the trigger name.
Example
The Pre-Text-Item trigger fires just before entering a text item.
When Do Navigation Triggers Not Fire?
The Pre- and Post- navigation triggers do not fire if they belong to a unit that is smaller than the current validation unit. For instance, if the validation unit is Record, Pre- and Post-Text-Item triggers do not fire.
What Happens When a Navigation Trigger Fails?
If a Pre- or Post navigation trigger fails, the input focus returns to its initial location (where it was prior to the trigger firing). To the user, it appears that the input focus has not moved at all.
1. Pre-Form
Perform an action just before Form Builder navigates to the form from "outside" the form,
such as at form startup.
IF not (DBMS_SESSION.IS_ROLE_ENABLED(’ADMINISTRATIVE’) or
(DBMS_SESSION.IS_ROLE_ENABLED(’TECHNICAL’) THEN
MESSAGE(’You are not authorized to run this application’);
PAUSE;
RAISE form_trigger_failure;
END IF;
2. Pre-Block
Perform an action before Form Builder navigates to the block level from the form level.
Fires during the Enter the Block process, during navigation from one block to another.
Usage Notes:
Use a Pre–Block trigger to:
· Allow or disallow access to a block
· Set variable values
Disabling stock_button when leaving CONTROL block:
begin
SET_ITEM_PROPERTY(’CONTROL.stock_button’, enabled, property_false);
End;
3. Pre-Record
Perform an action before Form Builder navigates to the record level from the block level.
Usage Notes:
· Fires during the Enter the Record process, during navigation to a different record.
· Use a Pre–Record trigger to keep a running total.
The following trigger prevents the user from entering a new record given some dynamic condition and the status of SYSTEM.RECORD_STATUS evaluating to NEW.
IF (( dynamic–condition) AND :System.Record_Status = ’NEW’) THEN
RAISE Form_Trigger_Failure;
END IF;
4. Pre-Text-Item
Perform an action before Form Builder navigates to a text item from the record level.
Usage Notes:
Use a Pre–Text–Item trigger to perform the following types of tasks:
· Derive a complex default value, based on other items previously
entered into the same record.
Record the current value of the text item for future reference, and
· store that value in a global variable or form parameter.
5. Post-Form
Perform an action before Form Builder navigates to "outside" the form, such as when exiting the form.
Fires during the Leave the Form process, when a form is exited.
Usage Notes:
You can use a Post–Form trigger for the following tasks:
· To clean up the form before exiting. For example, use a Post–Form trigger to erase any global variables that the form no longer requires.
· To display a message to the operator upon form exit.
This trigger does not fire when the form is exited abnormally, for example, if validation fails in the form.
6. Post-Block
Manipulate the current record when Form Builder leaves a block and navigates to the form level.
Usage Notes:
· Use a Post–Block trigger to validate the block’s current record; that is, the record that had input focus when the Leave the Block event occurred.
· You might also use this trigger to test a condition and prevent the user from leaving a block based on that condition.
Example
Set_item_property (‘control.cmdsave’, enabled, property_false);
7. Post-Record
Manipulate a record when Form Builder leaves a record and navigates to the block level.
Fires during the Leave the Record process. Specifically, the Post–Record trigger fires whenever the operator or the application moves the input focus from one record to another. The Leave the Record process can occur as a result of numerous operations, including INSERT_RECORD, DELETE_RECORD, NEXT_RECORD, NEXT_BLOCK, CREATE_RECORD, PREVIOUS_BLOCK, etc.
Usage Notes:
Use a Post–Record trigger when you want to perform an action whenever the operator or the application moves the input focus from one record to another. For example, if you want to set a visual attribute for an item as the operator scrolls down through a set of records, you can do so from within this trigger.
Example:
/* ** Built–in: NEXT_RECORD
** Example: If the current item is the last item in the ** block, then skip to the next record instead of ** the default of going back to the first item in ** the same block
** Trigger: Key–Next–Item */
DECLARE
cur_itm VARCHAR2(80) := :System.Cursor_Item;
cur_blk VARCHAR2(80) := :System.Cursor_Block;
lst_itm VARCHAR2(80);
BEGIN
lst_itm := cur_blk||’.’||Get_Block_Property(cur_blk,LAST_ITEM);
IF cur_itm = lst_itm THEN
Next_Record;
ELSE
Next_Item;
END IF;
END;
8. Post-Text-Item
Manipulate an item when Form Builder leaves a text item and navigates to the record level.
Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
II] WHEN-NEW-INSTANCE-TRIGGERS
Fire at the end of a navigational sequence that places the input focus on a different item. Specifically, these triggers fire just after Form Builder moves the input focus to a different item, when the form returns to a quiet state to wait for operator input.
When Do When-New- “object”-Instance Triggers Fire?
The When-New-”object”-Instance triggers fire immediately after navigation to the object specified as part of the trigger name.
Example
The When-New-Item-Instance trigger fires immediately after navigation to a new instance of an item.
1. When-New-Form-Instance
Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
Perform a query of all orders, when the ORDERS form is run, by including the following code in your When-New-Form-Instance trigger:
1a. EXECUTE_QUERY;
1b. BEGIN
DEFAULT_VALUE(’’, ’'GLOBAL.where_cls’);
SET_BLOCK_PROPERTY(’prod_lov_blk’, DEFAULT_WHERE,:GLOBAL.where_cls);
EXECUTE_QUERY;
END;
1c. BEGIN
EXECUTE_QUERY;
:GLOBAL.width_win_order := GET_WINDOW_PROPERTY(’win_order’, WIDTH);
:GLOBAL.height_win_order := GET_WINDOW_PROPERTY(’win_order’,HEIGHT);
:GLOBAL.width_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,WIDTH);
:GLOBAL.height_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,HEIGHT);
END;
1d. When-New-Form-Instance Trigger at Form Level
BEGIN
SET_WINDOW_PROPERTY( forms_mdi_window, WINDOW_STATE, MAXIMIZE );
SET_WINDOW_PROPERTY( forms_mdi_window, TITLE, ’Summit Sporting Goods Application’);
END;
Example
This code could be used in a WHEN-NEW-FORM-INSTANCE trigger to initially populate the hierarchical tree with data. The example locates the hierarchical tree first. Then, a record group is created and the hierarchical tree is populated.
DECLARE
htree ITEM;
v_ignore NUMBER;
rg_emps RECORDGROUP;
BEGIN
htree := Find_Item('tree_block.htree3');
rg_emps := Create_Group_From_Query('rg_emps',’select 1, level, ename, NULL, to_char(empno) ’ ||' from emp ' ||'connect by prior empno = mgr ' ||’start with job = ’’PRESIDENT’’’);
v_ignore := Populate_Group(rg_emps);
Ftree.Set_Tree_Property(htree, Ftree.RECORD_GROUP, rg_emps);
END;
2. When-New-Block-Instance
Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
Usage Notes:
Use a When–New–Block–Instance trigger to perform an action every time Oracle Forms instantiates a new block.
Example
The following example of a When-New-Block-Instance trigger conditionally sets the DELETE ALLOWED property to FALSE.
IF GET_APPLICATION_PROPERTY(username) = ’SCOTT’ THEN
SET_BLOCK_PROPERTY(’S_ITEM’,DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
3. When-New-Record-Instance
Perform an action immediately after the input focus moves to an item in a different record. If the new record is in a different block, fires after the When-New-Block-Instance trigger, but before the When-New-Item-Instance trigger.
Usage Notes:
Use a When–New–Record–Instance trigger to perform an action every time Oracle Forms instantiates a new record. For example, when an operator presses [Down] to scroll through a set of records, Oracle Forms fires this trigger each time the input focus moves to the next record, in other words, each time Oracle Forms instantiates a new record in the block.
Example
The Cursor arrives in each record of the S_Item block, and populates the product_image item with a picture of the products, if one exists
1] Declare
Filename varchar2(20);
Begin
Filename:=get_product_image(:S_ITEM.PRODUCT_ID);
If Filename= ‘ No file’ then
Null;
Else
Read_Image_File(filename,’tiff’,’S_ITEM.product_image’);
End if
End;
2] 1.a Example: Brings up the debugging window for a particular ** value of the ’JOB’ item anytime the user
changes records.*/
BEGIN
IF :Emp.Job = ’CLERK’ THEN
Break;
Call_Form(’clerk_timesheet’);
Break;
END IF;
END;
3.] BEGIN
IF (:global.cancel_query = 'Y' and :system.mode = 'ENTER-QUERY') THEN
Exit_Form;
:global.cancel_query = 'N';
END IF;
END;
4.When-New-Item-Instance
Fires when the input focus moves to an item. Specifically, it fires after navigation to an item, when Form Builder is ready to accept input in an item that is different than the item that previously had input focus.
Perform an action immediately after the input focus moves to a different item. If the new item is in a different block, fires after the When-New-Block-Instance trigger.
Usage Notes
Use a When-New-Item-Instance trigger to perform an action whenever an item gets input focus. The When-New-Item-Instance trigger is especially useful for calling restricted (navigational) built-ins.
1] IF CHECKBOX_CHECKED(’S_ORD.order_filled’)THEN
SET_ITEM_PROPERTY(’S_ORD.date_shipped’,UPDATE_ALLOWED, property_true);
GO_ITEM(’S_ORD.date_shipped’);
END IF;
2] Built–in: CLEAR_ITEM - Example: Clear the current item if it does not represent ** the first day of a
month.
BEGIN
IF TO_CHAR(:Emp.Hiredate,’DD’) <> ’01’ THEN
Clear_Item;
Message(’This date must be of the form 01–MON–YY’);
END IF;
END;
3]. BEGIN
IF :Emp.Empno IS NOT NULL THEN
:Global.Employee_Id := :Emp.Empno;
Clear_Block(No_Validate);
END IF;
END;
4]. Assume that you want Oracle Forms to display an LOV when the operator enters query mode and the
input focus is in a particular text item. The following trigger accomplishes that operation.
BEGIN
IF :System.Cursor_Item = ’EMP.EMPNO’ and :System.Mode = ’ENTER–QUERY’ THEN
IF NOT Show_Lov(’my_lov’) THEN
RAISE Form_Trigger_Failure;
End if;
END IF;
END;
H. VALIDATION TRIGGERS
Validation triggers fire when Form Builder validates data in an item or record. Form Builder performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
Validation occurs at item, record, block, and form levels.
• Validation happens when:
– [Enter] Key or ENTER built-in is activated
– Control leaves the validation unit due to navigation or commit
Validation Process
Form Builder performs a validation process at several levels to ensure that records and individual values follow appropriate rules. If validation fails, then control is passed back to the appropriate level, so that the operator can make corrections. Validation occurs at:
• Item level:
Form Builder records a status for each item to determine whether it is currently valid. If an item has been changed and is not yet marked as valid, then Form Builder first performs standard validation
checks to ensure that the value conforms to the item’s properties. These checks are carried out before firing any When-Validate-Item triggers that you have defined. Standard checks include the following:
- Format mask
- Required (if so, then is the item null?)
- Data type
- Range (Lowest-Highest Allowed Value)
- Validate from List (see later in this lesson)
• Record level:
After leaving a record, Form Builder checks to see whether the record is valid. If not, then the status of each item in the record is checked, and a When-Validate-Record trigger is then fired, if present. When the record passes these checks, it is set to valid.
• Block and form level:
At block or form level, all records below that level are validated. For example, if you commit (save) changes in the form, then all records in the form are validated, unless you have suppressed
this action.
When Does Validation Occur?
Form Builder carries out validation for the validation unit under the following conditions:
• The [Enter] key is (ENTER command is not necessary mapped to the key that is physically labeled Enter) pressed or the ENTER built-in procedure is run (whose purpose is to force validation immediately).
• The operator or a trigger navigates out of the validation unit. This includes when changes are committed. The default validation unit is item, but can also be set to record, block, or form by the designer. The validation unit is discussed in the next section.
Using LOVs for Validation
When you attach an LOV to a text item by setting the LOV property of the item, you can optionally use the LOV contents to validate data entered in the item. Do this by setting the Validate from List property to Yes for the item. At validation time, Form Builder then automatically uses the item value as a non case-sensitive search string on the LOV contents. The following events then occur, depending on the
circumstances:
• If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally.
• If the item’s value causes a single record to be found in the LOV, but is a partial value of the LOV value, then the full LOV column value is returned to the item (providing that the item is defined as the return item in the LOV). The item then passes this validation phase.
• If the item value causes multiple records to be found in the LOV, Form Builder displays the LOV and uses the text item value as the search criteria to automatically reduce the list, so that the operator must choose.
• If no match is found, then the full LOV contents are displayed to the operator.
Validation Triggers
• Item level
When-Validate-Item
• Block level
When-Validate-Record
1. When – Validate -Item
Fires during the Validate the Item process. Specifically, it fires as the last part of item validation for items with the New or Changed validation status.
Usage Notes
· Use a When-Validate-Item trigger to supplement Form Builder default item validation processing.
· It is possible to write a When-Validate-Item trigger that changes the value of an item that Form Builder is validating. If validation succeeds, Form Builder marks the changed item as Valid and does not re-validate it. While this behavior is necessary to avoid validation loops, it does make it possible for your application to commit an invalid value to the database.
· The Defer_Required_Enforcement property postpones enforcement of the Required property from item validation to record validation. When an item has the Required property set to Yes, by default Form Builder will not allow navigation out of the item until a valid value is entered. Setting the Defer_Required_Enforcement property to Yes allows the operator to move freely among the items in the record.
When-Validate-Item Trigger
You have already used this trigger to add item-level validation. The trigger fires after standard item validation, and input focus is returned to the item if the trigger fails.
Example
The SELECT...INTO statement must return an error if more than one row is retrieved that matches the criteria. This implies PL/SQL may attempt to fetch data twice from the table in question to insure that there aren't two matching rows.
BEGIN
SELECT description INTO :Employee.Commplan_Desc FROM commplan WHERE commcode = :Employee.Commcode;
EXCEPTION
WHEN No.Data_Found THEN
Message('Invalid Commission Plan, Use <List> for help');
RAISE Form_Trigger_Failure;
WHEN Too_Many_Rows THEN
Message('Error. Duplicate entries in COMMPLAN table!');
RAISE Form_Trigger_Failure;
END;
2. When – Validate -Record
Fires during the Validate the Record process. Specifically, it fires as the last part of record
validation for records with the New or Changed validation status.
Use a When-Validate-Record trigger to supplement Form Builder default record validation
processing.
Note that it is possible to write a When-Validate-Record trigger that changes the value of an
item in the record that Form Builder is validating. If validation succeeds, Form Builder marks
the record and all of the fields as Valid and does not re-validate. While this behavior is
necessary to avoid validation loops, it does make it possible for your application to commit an
invalid value to the database.
When-Validate-Record Trigger
This trigger fires after standard record-level validation, when the operator has left a new or changed record. Because Form Builder has already checked that required items for the record are valid, you can use this trigger to perform additional checks that may involve more than one of the record’s items, in the order they were entered. When-Validate-Record must be defined at block level or above.
Example
The following example verifies that Start_Date is less than End_Date. Since these two text items have values that are related, it's more convenient to check the combination of them once at the record level, rather than check each item separately. This code presumes both date items are mandatory and that neither will be NULL.
/* Method 1: Hardcode the item names into the trigger. ** Structured this way, the chance this code will ** be reusable in other forms we write is pretty low because of dependency on block and item ** names.*/
BEGIN
IF :Experiment.Start_Date > :Experiment.End_Date THEN
Message('Your date range ends before it starts!');
RAISE Form_Trigger_Failure;
END IF;
END;
I.TRANSACTIONAL TRIGGERS
Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
Transaction processing includes two phases:
• Post:
– Writes record changes to base tables
– Fires transactional triggers
• Commit: Performs database commit
Errors result in:
• Rollback of the database changes
• Error message
The Commit Sequence of Events
The commit sequence of events (when the Array DML size is 1) is as follows:
1 Validate the form.
2 Process save point.
3 Fire the Pre-Commit trigger.
4 Validate the block (for all blocks in sequential order).
For all deleted records of the block (in reverse order of deletion):
- Fire the Pre-Delete trigger.
- Delete the row from the base table or fire the On-Delete trigger.
- Fire the Post-Delete trigger.
For all inserted or updated records of the block in sequential order:
If it is an inserted record:
- Copy Value From Item.
- Fire the Pre-Insert trigger.
- Check the record uniqueness.
- Insert the row into the base table or fire the On-Insert trigger.
- Fire the Post-Insert trigger.
If it is an updated record:
- Fire the Pre-Update trigger.
- Check the record uniqueness
- Update the row in the base table or fire the On-Update trigger.
- Fire the Post-Update trigger.
5 Fire the Post-Forms-Commit trigger.
If the current operation is COMMIT, then:
6 Issue an SQL-COMMIT statement.
7 Fire the Post-Database-Commit trigger.
Commit Triggers Uses
1. Pre-Commit
Check user authorization; set up special locking
Pre-Commit Fires once during commit processing, before base table blocks are processed; fires if there are changes to base table items in the form or if changes have been posted but not yet committed (This trigger always fires in case of uncommitted posts, even if there are no changes to post.)
Usage Notes:
Use a Pre–Commit trigger to perform an action, such as setting up special locking requirements, anytime a database commit is going to occur.
Pre-Commit: Fires once if form changes are made or uncommitted changes are posted
2. Pre-Delete
Journaling; implement foreign-key delete rule
Fires during the Post and Commit Transactions process, before a row is deleted. It fires once for each record that is marked for delete.
Usage Notes
· Use a Pre-Delete trigger to delete the detail record of a master record.
· Use a Pre-Delete trigger to prevent the deletion of a record if that record is the master record for detail records that still exist.
PRE-DELETE TRIGGER -- Final checks before row deletion
DECLARE
CURSOR C1 IS SELECT ’anything’ FROM S_ORD WHERE customer_id = :S_CUSTOMER.id;
BEGIN
OPEN C1;
FETCH C1 INTO :GLOBAL.dummy;
IF C1%FOUND THEN
MESSAGE(’There are orders for this customer!’);
RAISE form_trigger_failure;
ELSE
CLOSE C1;
END IF;
END;
3. Pre-Insert
Fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert.
Generate sequence numbers; journaling; automatically generated columns; check constraints
Usage Notes:
Use a Pre–Insert trigger to perform the following tasks:
· change item values
· keep track of the date a record is created and store that in the record prior to committing
Example:1
This Pre-Insert trigger on the S_ORD block assigns an Order ID from the sequence S_ORD_ID, which will be written to the ID column when the row is subsequently inserted.
Begin
SELECT S_ORD_ID.nextval INTO :S_ORD.id FROM SYS.dual;
End;
Note:
The Insert Allowed and Keyboard Navigable properties on :S_ORD.id should be No, so that the user does not enter an ID manually.
You can also assign sequence numbers from a table. If you use this method, then two transactional triggers are usually involved:
• Use Pre-Insert to select the next available number from the sequence table (locking the row to prevent other users from selecting the same value) and increment the value by the required amount.
• Use Post-Insert to update the sequence table, recording the new upper value for the sequence.
Example:2
This example assigns a primary key field based on a sequence number, and then writes a row into an auditing table, flagging creation of a new order.
DECLARE
CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;
BEGIN
/* ** Fetch the next sequence number from the ** explicit cursor directly into the item in
** the Order record. Could use SELECT...INTO, ** but explicit cursor is more efficient. */
OPEN next_ord;
FETCH next_ord INTO :Order.OrderId;
CLOSE next_ord;
/* ** Make sure we populated a new order id ok... */
IF :Order.OrderId IS NULL THEN
Message(’Error Generating Next Order Id’);
RAISE Form_Trigger_Failure;
END IF;
/* ** Insert a row into the audit table */
INSERT INTO ord_audit( orderid, operation, username, timestamp )
VALUES ( :Order.OrderId, ’New Order’, USER,SYSDATE );
END;
4. Pre-Update
Fires during the Post and Commit Transactions process, before a row is updated. It fires once for each record that is marked for update.
Journaling; implement foreign-key update rule; auto-generated columns; check constraints
Usage Notes:
Use a Pre–Update trigger to audit transactions.
Example:
The following example writes a row into an Audit Table showing old discount and new discount for a given customer, including timestamp and username making the change.
DECLARE
old_discount NUMBER;
new_discount NUMBER := :Customer.Discount_Pct;
oper_desc VARCHAR2(80);
CURSOR old_value IS SELECT discount_pct FROM customer WHERE CustId = :Customer.CustId;
BEGIN
/* ** Fetch the old value of discount percentage from the database by CustomerId. We need to do this since the value of :Customer.Discount_Pct will be the *new* value we’re getting ready to commit and we want to record for posterity the old and new values. We could use SELECT...INTO but choose an explicit cursor for efficiency. */
OPEN old_value;
FETCH old_value INTO old_discount;
CLOSE old_value;
/* ** If the old and current values are different, then we need to write out an audit record */
IF old_discount <> new_discount THEN
/* Construct a string that shows the operation of Changing the old value to the new value. e.g.
** ’Changed Discount from 13.5% to 20%’ */
oper_desc := ’Changed Discount from ’||
TO_CHAR(old_discount)||’% to ’||
TO_CHAR(new_discount)||’%’;
/* ** Insert the audit record with timestamp and user */
INSERT INTO cust_audit( custid, operation, username, timestamp ) VALUES ( :Customer.CustId,oper_desc,USER,SYSDATE );
END IF;
END;
5. On-Commit
Fires whenever Oracle Forms would normally issue a database commit statement to finalize a transaction. By default, this operation occurs after all records that have been marked as updates, inserts, and deletes have been posted to the database.
Usage Notes:
· Use an On–Commit trigger to change the conditions of normal Oracle Forms commit processing to fit the particular requirements of a commit to a non–ORACLE database.
· To perform the default processing from this trigger, call to the COMMIT_FORM built–in.
Example:
This example disables the commit operation when running against a datasource that does not support transaction control. If the application is running against ORACLE, the commit operation behaves normally.
BEGIN
IF Get_Application_Property(DATA_SOURCE) = ’ORACLE’ THEN
Commit_Form;
END IF; /* ** otherwise, no action is performed */
END;
6. On-Delete
Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre–Delete trigger fires and before the Post–Delete trigger fires, replacing the actual database delete of a given row. The trigger fires once for each row that is marked for deletion from the database.
Usage Notes:
· Use an On–Delete trigger to replace the default Oracle Forms processing for handling deleted records during transaction posting.
· To perform the default Oracle Forms processing from this trigger, that is, to delete a record from your form or from the database, include a call to the DELETE_RECORD built–in.
Example:
This example updates the employee table to set the Termination_Date, rather than actually deleting the employee from the database.
BEGIN
UPDATE emp SET termination_date = SYSDATE WHERE empno = :Emp.Empno;
END;
7. On-Insert
Fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.
Usage Notes
· Use an On-Insert trigger to replace the default Form Builder processing for handling inserted records during transaction posting.
· To perform the default Form Builder processing from this trigger, include a call to the INSERT_RECORD built-in.
Description
When called from an On-Insert trigger, inserts the current record into the database during Post and Commit Transactions processing. This built-in is included primarily for applications that will run against a non-ORACLE datasource.
Syntax
PROCEDURE INSERT_RECORD;
/* ** Built-in: INSERT_RECORD ** Example : Perform Form Builder standard insert processing ** based on a global flag setup at startup by the ** form, perhaps based on a parameter. ** Trigger: On-Insert */
BEGIN
/* ** Check the global flag we setup at form startup */
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_insrec block=EMP');
/* ** Otherwise, do the right thing. */
ELSE
Insert_Record;
END IF;
END;
8. On-Update
Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre–Update trigger fires and before the Post–Update trigger fires, when Oracle Forms would normally update a record in the database. It fires once for each row that is marked for update in the form.
Usage Notes:
· Use an On–Update trigger to replace the default Oracle Forms processing for handling updated records during transaction posting.
· To perform the default Oracle Forms processing from this trigger, include a call to the UPDATE_RECORD built–in.
Begin
UPDATE RECORD;
End;
When called from an On-Update trigger, initiates the default Form Builder processing for updating a record in the database during the Post and Commit Transaction process. This built-in is included primarily for applications that run against a non-ORACLE data source.
DML Statements Issued During Commit Processing
INSERT INTO base_table ( base_column, base_column,...) VALUES ( :base_item, :base_item, ...)
UPDATE base_table SET base_column = :base_item, base_column = :base_item, ...WHERE ROWID = :ROWID
DELETE FROM base_table WHERE ROWID = :ROWID
DML Statements Issued During Commit Processing Rules:
• DML statements may fire database triggers.
• Form Builder uses and retrieves ROWID.
• The Update Changed Columns Only and Enforce Column Security properties affect UPDATE
statements.
• Locking statements are not issued.
9. Post – Database Commit
Description
Fires once during the Post and Commit Transactions process, after the database commit occurs. Note that the Post-Forms-Commit trigger fires after inserts, updates, and deletes have been posted to the database, but before the transaction has been finalized by issuing the Commit. The Post-Database-Commit Trigger fires after Form Builder issues the Commit to finalize the transaction.
Usage Notes
Use a Post-Database-Commit trigger to perform an action anytime a database commit has occurred.
Example
/*
** FUNCTION recs_posted_and_not_committed
** RETURN BOOLEAN IS
BEGIN
Default_Value('TRUE','Global.Did_DB_Commit');
RETURN (:System.Form_Status = 'QUERY' AND :Global.Did_DB_Commit = 'FALSE');
END;
*/
BEGIN
:Global.Did_DB_Commit := 'FALSE';
END;
Post-Database-Commit Determines if commit was successful; determines if there are
posted, uncommitted changes
10. Post – Form - Commit
Fires once during the Post and Commit Transactions process. If there are records in the form
that have been marked as inserts, updates, or deletes, the Post-Forms-Commit trigger fires
after these changes have been written to the database but before Form Builder issues the
database Commit to finalize the transaction.
If the operator or the application initiates a Commit when there are no records in the form
have been marked as inserts, updates, or deletes, Form Builder fires the Post-Forms-Commit
trigger immediately, without posting changes to the database.
Usage Notes
· Use a Post-Forms-Commit trigger to perform an action, such as updating an audit trail, anytime a database commit is about to occur.
· Post-Forms-Commit Checks complex multirow constraints
Example
This example can be used in concert with the Post-Database-Commit trigger to detect if records
have been posted but not yet committed.
/* FUNCTION recs_posted_and_not_committed
RETURN BOOLEAN IS
BEGIN
Default_Value('TRUE','Global.Did_DB_Commit');
RETURN (:System.Form_Status = 'QUERY'AND :Global.Did_DB_Commit = FALSE');
END;
*/
BEGIN
:Global.Did_DB_Commit := 'FALSE';
END;
11. Post – Delete
Fires during the Post and Commit Transactions process, after a row is deleted. It fires once for each row that is deleted from the database during the commit process. form or block
Usage Notes:
Use a Post–Delete trigger to audit transactions.
Example - 1
Begin
INSERT INTO delete_audit (id, timestamp, who_did_it)VALUES ( :S_ORD.id, SYSDATE, USER );
End;
Example - 2
Begin
Delete from S_ORD SET WHERE id = :S_ORD.id;
IF SQL%NOTFOUND THEN
MESSAGE(’Record not found in database’);
RAISE form_trigger_failure;
Else
Messafe(SQL%rowcount|| “ rows Deleted”);
END IF;
End;
12. Post – Insert
Fires during the Post and Commit Transactions process, just after a record is inserted. It fires
once for each record that is inserted into the database during the commit process.
· Use a Post-Insert trigger to audit transactions.
· Write changes to nonbase tables.
· Gather statistics on applied changes.
Example 1
Keeping an Audit Trail
:GLOBAL.insert_tot := TO_CHAR(TO_NUMBER(:GLOBAL.insert_tot)+1);
Example 2
To handle exceptions, include EXCEPTION section in trigger. Post-Insert trigger:
Begin
INSERT INTO LOG_TAB (LOG_VAL, LOG_USER) VALUES(:S_DEPT.id,:GLOBAL.username);
EXCEPTION
WHEN OTHERS THEN
MESSAGE(’Error! ’,||SQLERRM);
End;
13. Post – Update
Fires during the Post and Commit Transactions process, after a row is updated. It fires once for each row that is updated in the database during the commit process.
Usage Notes: Use a Post–Update trigger to audit transactions.
This Post-Update trigger writes the current record ID to the UPDATE_AUDIT table,along with a time stamp and the user who performed the update.
Example - 1
Begin
INSERT INTO update_audit (id, timestamp, who_did_it)VALUES ( :S_ORD.id, SYSDATE, USER );
End;
Example - 2
Begin
UPDATE S_ORD SET date_shipped = SYSDATE WHERE id = :S_ORD.id;
IF SQL%NOTFOUND THEN
MESSAGE(’Record not found in database’);
RAISE form_trigger_failure;
END IF;
End;
Query Processing Triggers Uses
14. Pre – Select
Fires during Execute Query and Count Query processing, after Form Builder constructs the SELECT statement to be issued, but before the statement is actually issued. Note that the SELECT statement can be examined in a Pre-Select trigger by reading the value of the system variable SYSTEM.LAST_QUERY
Fires after Form Builder has constructed the block SELECT statement based on the query conditions, but before it issues this statement
Use a Pre-Select trigger to prepare a query prior to execution against a non-ORACLE data source.
This example assigns a primary key field based on a sequence number, and then writes a row into an auditing table, flagging creation of a neworder.
DECLARE
CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;
BEGIN
/** Fetch the next sequence number from the Explicit cursor directly into the item in the Order record. Could use SELECT...INTO, ** but explicit cursor is more efficient. */
OPEN next_ord;
FETCH next_ord INTO :Order.OrderId;
CLOSE next_ord;
IF :Order.OrderId IS NULL THEN
Message(’Error Generating Next Order Id’);
RAISE Form_Trigger_Failure;
END IF;
/** Insert a row into the audit table
INSERT INTO ord_audit( orderid, operation, username, timestamp )
VALUES ( :Order.OrderId,’New Order’,USER,SYSDATE );
END;
15. On - Select
Fires when Form Builder would normally execute the open cursor, parse, and execute phases
of a query, to identify the records in the database that match the current query criteria.
On-Select replaces open cursor, parse, and execute phases.
Usage Notes
· Use an On-Select trigger to open and execute the database cursor. Specifically, use this trigger
when you are retrieving data from a non-ORACLE data source. The On-Select trigger can be
used in conjunction with the On-Fetch trigger to replace the processing that normally occurs in
the EXECUTE_QUERY built-in subprogram.
· To perform the default Form Builder processing from this trigger, include a call to the
SELECT_RECORDS built-in.
Example - 1
In the following example, the On-Select trigger is used to call a user exit, 'Query,' and a built-in subprogram, SELECT_RECORDS, to perform a query against a database.
Begin
IF Get_Application_Property(DATASOURCE) = 'DB2' THEN
User_Exit ( 'Query' );
IF Form_Failure OR Form_Fatal THEN
ABORT_QUERY;
END IF;
ELSE
/* ** Perform the default Form Builder task of opening the query. */
Select_Records;
END IF;
End;
16. Post-Select Trigger
Description
Fires after Form Builder has constructed and issued the block SELECT statement, but before it fetches the records
The Post-Select trigger fires after the default selection phase of query processing, or after the
successful execution of the On-Select trigger. It fires before any records are actually retrieved
through fetch processing.
Usage Note:
Use the Post-Select trigger to perform an action based on the outcome of the Select phase of query processing such as an action based on the number of records that match the query criteria.
15. On – Fetch
Fires when Form Builder performs a fetch for a set of rows (You can use the CREATE_QUERIED_RECORD built-in to create queried records if you want to replace default fetch processing.)
• On-Fetch continues to fire until:
– It fires without executing CREATE_QUERIED_RECORD.
– The query is closed by the user or by ABORT_QUERY.
– It raises FORM_TRIGGER_FAILURE.
The trigger will fire once for each record that is to be fetched. On–Fetch:
DECLARE
j NUMBER := Get_Block_Property(blk_name, RECORDS_TO_FETCH);
emprow emp%ROWTYPE;
BEGIN
FOR ctr IN 1..j LOOP
/* ** Try to get the next row. */
EXIT WHEN NOT MyPackage.Get_Next_Row(emprow);
Create_Queried_Record;
:Emp.rowid := emprow.ROWID;
:Emp.empno := emprow.EMPNO;
:Emp.ename := emprow.ENAME;
END LOOP;
IF form_fatal OR form_failure THEN
raise form_trigger_failure;
END IF;
END;
16. On – Count
Fires when Form Builder would usually perform default Count Query processing to determine the number of rows that match the query conditions
Fires when Form Builder would normally perform default Count Query processing to determine the number of rows in the database that match the current query criteria. When the
On-Count trigger completes execution, Form Builder issues the standard query hits message:
FRM-40355: Query will retrieve <n> records.
Usage Notes
· Use an On-Count trigger to replace default Count Query processing in an application running against a non-ORACLE data source.
· To perform the default Form Builder processing from this trigger, include a call to the built-in.
· If you are replacing default processing, you can set the value of the Query_Hits block property to indicate the number of records in the non-ORACLE data source that match the query criteria.
· Form Builder will display the query hits message (FRM-40355) even if the On-Count trigger fails to set the value of the Query_Hits block property. In such a case, the message reports 0 records identified.
Example - 1
This example calls a user-named subprogram to count the number of records to be retrieved by
the current query criteria, and sets the Query_Hits property appropriately.
DECLARE
j NUMBER;
BEGIN
j := Recs_Returned('DEPT',Name_In('DEPT.DNAME'));
Set_Block_Property('DEPT',QUERY_HITS,j);
END;
Example 2
/* ** Built-in: COUNT_QUERY ** Example: Display the number of records that will be retrieved ** by the current query. */
BEGIN
Count_Query;
END;
Example 3
/* ** Built-in: COUNT_QUERY
** Example: Perform Form Builder count query hits processing. Decide whether to use this Built-in or a user
** exit based on a global flag setup at startup by the form, perhaps based on a parameter.
* Trigger: On-Count */
BEGIN
/* ** Check the global flag we set during form startup */
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
/* ** User exit returns query hits count back into the ** CONTROL.HITS item. */
User_Exit('my_count');
/* ** Deposit the number of query hits in the appropriate ** block property so Form Builder can display its normal ** status message. */
Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits);
/* ** Otherwise, do the right thing. */
ELSE
Count_Query;
END IF;
END;
17.On-Sequence-Number Trigger
Description
Fires when Form Builder would normally perform the default processing for generating sequence numbers for default item values. Replaces the default series of events that occurs
when Form Builder interacts with the database to get the next value from a SEQUENCE object
defined in the database.
Usage Notes
· When a SEQUENCE is used as a default item value, Form Builder queries the database to get the next value from the SEQUENCE whenever the Create Record event occurs. Suppress or override this functionality with an On-Sequence-Number trigger.
· To perform the default Form Builder processing from this trigger, call the GENERATE_SEQUENCE_NUMBER built-in.
Example:
/*
** Built–in: GENERATE_SEQUENCE_NUMBER
** Example: Perform Oracle Forms standard sequence number processing based on a global flag setup at
** startup by the form, perhaps based on a parameter.
** Trigger: On–Sequence–Number */
BEGIN
/* ** Check the global flag we setup at form startup */
IF :Global.Using_Transactional_Triggers = ’TRUE’ THEN
User_Exit(’my_seqnum seq=EMPNO_SEQ’);
/* ** Otherwise, do the right thing. */
ELSE
Generate_Sequence_Number;
END IF;
END;
18. On-Check-Unique Trigger
Description
During a commit operation, the On-Check-Unique trigger fires when Form Builder normally
checks that primary key values are unique before inserting or updating a record in a base table.
It fires once for each record that has been inserted or updated.
Replaces the default processing for checking record uniqueness. When a block has the
PRIMKEYB property set to Yes, Form Builder, by default, checks the uniqueness of a record by
constructing and executing the appropriate SQL statement to select for rows that match the
current record's primary key values. If a duplicate row is found, Form Builder displays
message FRM-40600: Record has already been inserted.
For a record that has been marked for insert, Form Builder always checks for unique primary
key values. In the case of an update, Form Builder checks for unique primary key values only
if one or more items that have the Primary Key item property have been modified.
Usage Notes
To perform the default processing from this trigger, call the CHECK_RECORD_UNIQUENESS
built-in.
On-Check-Unique Trigger examples
The following example verifies that the current record in question does not already exist in the
DEPT table.
DECLARE
CURSOR chk_unique IS SELECT 'x' FROM dept WHERE deptno = :dept.deptno;
tmp VARCHAR2(1);
BEGIN
OPEN chk_unique;
FETCH chk_unique INTO tmp;
CLOSE chk_unique;
IF tmp IS NOT NULL THEN
Message('This department already exists.');
RAISE Form_Trigger_Failure;
END IF;
END;
19. On-Close Trigger
Description
Fires when an operator or the application causes a query to close. By default, Form Builder
closes a query when all of the records identified by the query criteria have been fetched, or
when the operator or the application aborts the query.
The On-Close trigger augments the normal Form Builder "close cursor" phase of a query.
Usage Notes
· Use an On-Close trigger after using the On-Select or On-Fetch triggers, specifically, to close files, close cursors, and free memory.
· The On-Close trigger fires automatically when the ABORT_QUERY built-in is called from an On-Select trigger.
Example
The following example releases memory being used by a user-defined data access method via
the transactional triggers.
BEGIN
IF NOT my_data source_open('DX110_DEPT') THEN
my_datasource_close('DX110_DEPT');
END IF;
END;
20. On-Column-Security Trigger
Description
Fires when Form Builder would normally enforce column-level security for each block that has
the Enforce Column Security block property set On.
By default, Form Builder enforces column security by querying the database to determine the
base table columns to which the current form operator has update privileges. For columns to
which the operator does not have update privileges, Form Builder makes the corresponding
base table items in the form non-updateable by setting the Update Allowed item property Off
dynamically. Form Builder performs this operation at form startup, processing each block in
sequence.
Usage Notes
To perform the default processing from this trigger, call the ENFORCE_COLUMN_SECURITY
built-in.
Example
The following example sets salary and commission text items in the current block to disabled
and non-updateable, unless the SUPERUSER role is enabled. Only users with the user-defined
SUPERUSER role can change these number fields.
DECLARE
itm_id Item;
on_or_off NUMBER;
BEGIN
IF NOT role_is_set('SUPERUSER') THEN
on_or_off := PROPERTY_OFF;
ELSE
on_or_off := PROPERTY_ON;
END IF;
itm_id := Find_Item('Emp.Sal');
Set_Item_Property(itm_id,ENABLED,on_or_off);
Set_Item_Property(itm_id,UPDATEABLE,on_or_off);
itm_id := Find_Item('Emp.Comm');
Set_Item_Property(itm_id,ENABLED,on_or_off);
Set_Item_Property(itm_id,UPDATEABLE,on_or_off);
END;
21. On-Rollback Trigger
Description
Fires when Form Builder would normally issue a ROLLBACK statement, to roll back a
transaction to the last savepoint that was issued.
Usage Notes
Use an On-Rollback trigger to replace standard Form Builder rollback processing.
To perform default Form Builder processing from this trigger, include a call to the
ISSUE_ROLLBACK built-in.
ISSUE_ROLLBACK examples
/* ** Built-in: ISSUE_ROLLBACK
** Example: Perform Form Builder standard Rollback processing.
** Decide whether to use this built-in based on a ** global flag setup at startup by the form. ** perhaps based on a parameter.
** Trigger: On-Rollback */
DECLARE
sp_name VARCHAR2(80);
BEGIN
/* Get the name of the savepoint to which Form Builder needs to ** rollback. (NULL = Full Rollback)*/
sp_name := Get_Application_Property(SAVEPOINT_NAME);
/* ** Check the global flag we setup at form startup */
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_rollbk name='||sp_name);
ELSE
Issue_Rollback(sp_name);
END IF
;
END;
J.LOGON TRANSACTION TRIGGERS
1. Pre-Logon Trigger
Fires just before Form Builder initiate a logon procedure to the data source.
Usage Notes
Use a Pre-Logon trigger to prepare the form for the logon procedure, particularly to a non- ORACLE data source.
2. On-Logon Trigger
Fires once per logon when Oracle Forms normally initiates the logon sequence.
Usage Notes:
Use an On–Logon trigger to initiate a logon procedure to a non–ORACLE data source.
· Pre–Logon and Post–Logon triggers fire as part of the logon procedure.
· You can supply a NULL command to this trigger to bypass the connection to a data source, if you want to create an application that does not require a data source.
To perform the default Oracle Forms processing from this trigger, include a call to the LOGON built–in.
DECLARE
connected BOOLEAN:=FALSE;
tries NUMBER:=3;
un NUMBER;
pw VARCHAR2(30);
cs VARCHAR2(30);
BEGIN
SET_APPLICATION_PROPERTY(CURSOR_STYLE,’DEFAULT’);
WHILE CONNECTED = FALSE AND tries > 0
LOOP
LOGON_SCREEN;
un:=GET_APPLICATION_PROPERTY( USERNAME );
pw:=GET_APPLICATION_PROPERTY( PASSWORD );
cs:= GET_APPLICATION_PROPERTY( CONNECTION_STRING );
LOGON( un, pw || ‘@’ || CS , FALSE );
IF FORM_SUCESS THEN
Connected:=TRUE;
END IF;
Tries:=tries-1;
END LOOP;
IF NOT CONNECTED THEN
MESSAGE(‘Too many tries’);
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
3. POST-LOGON TRIGGER
Description
Fires after either of the following events:
· The successful completion of Form Builder default logon processing.
· The successful execution of the On-Logon trigger.
Example
This example calls a user exit to log the current username and time to an encrypted audit trail file on the file system, which for security reasons is outside the database.
BEGIN
User_Exit('LogCrypt '|| USER||' ' ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));
END;
4. Pre-Logout Trigger
Fires once before Form Builder initiate a logout procedure.
Usage Notes
· Use a Pre-Logout trigger to prepare the form for logging out from the data source, particularly a non-ORACLE data source.
· If you call certain built-ins from within one of the Logout triggers, the results are undefined. For example, the COPY built-in cannot be called from a Pre-Logout trigger because Pre-Logout fires after the Leave the Form event. Because the form is no longer accessible at this point, the COPY operation is not possible.
5. On-Logout Trigger
Fires when Form Builder normally initiates a logout procedure from Form Builder and from
the RDBMS.
Usage Notes
· Use an On-Logout trigger to replace the default logout processing either from the RDBMS or from a non-ORACLE data source.
· To perform the default Form Builder processing from this trigger, include a call to the LOGOUT built-in.
· If you call certain built-ins from within one of the Logout triggers, the results are undefined. For example, you cannot call the COPY built-in from a Pre-Logout trigger because Pre-Logout fires after the Leave the Form event. Because the form is no longer accessible, a COPY operation is not possible.
6. Post-Logout Trigger
Description
Fires after either of the following events:
· Form Builder successfully logs out of ORACLE.
· The successful execution of the On-Logout trigger.
Usage Notes
· Use a Post-Logout trigger to audit or to perform tasks on an Form Builder application that does
not require or affect the RDBMS or other data source.
· If you call certain built-ins from within one of the Logout triggers, the results are undefined. For
example, you cannot call COPY from a Pre-Logout trigger because Pre-Logout fires after the
Leave the Form event. Because the form is no longer accessible, a COPY operation is not possible.
This example calls a user exit to log the current username and time to an encrypted audit trail file on the file system, which for security reasons is outside the database.
BEGIN
User_Exit(’LogCrypt ’||USER||’ ’ ||
TO_CHAR(SYSDATE,’YYYYMMDDHH24MISS’));
END;
J.MOUSE TRIGGERS
1. When-Mouse-Click Trigger
Description
Fires after the operator click the mouse if one of the following events occurs:
· if attached to the form, when the mouse is clicked within any canvas or item in the form
· if attached to a block, when the mouse is clicked within any item in the block
· if attached to an item, when the mouse is clicked within the item
Three events must occur before a When-Mouse-Click trigger will fire:
· Mouse down
· Mouse up
· Mouse click
Any trigger that is associated with these events will fire before the When-Mouse-Click trigger
fires.
Usage Notes
Use the When-Mouse-Click trigger to perform an action every time the operator clicks the mouse within an item and/or canvas.
2. When-Mouse-DoubleClick Trigger
Description
Fires after the operator double-clicks the mouse if one of the following events occurs:
· if attached to the form, when the mouse is double-clicked within any canvas or item in the form
· if attached to a block, when the mouse is double-clicked within any item in the block
· if attached to an item, when the mouse is double-clicked within the item
Six events must occur before a When-Mouse-DoubleClick trigger will fire:
· Mouse down
· Mouse up
· Mouse click
· Mouse down
· Mouse up
· Mouse double-click
Any trigger that is associated with these events will fire before the When-Mouse-DoubleClick
trigger fires.
Usage Notes
Use a When-Mouse-DoubleClick trigger to perform an action every time the operator DoubleClick the mouse within an item and/or canvas.
When-Mouse-DoubleClick Trigger examples
Example
Assume that an application requires Behavior A when the operator clicks the mouse and
Behavior B when the operator double-clicks the mouse. For example, if the operator clicks the
mouse, a product information window must appear. If the operator double-clicks the mouse,
an online help window must appear.
3. When-Mouse-Down Trigger
Description
Fires after the operator presses down the mouse button if one of the following events occurs:
· if attached to the form, when the mouse is pressed down within any canvas or item in the form
· if attached to a block, when the mouse is pressed down within any item in the block
· if attached to an item, when the mouse is pressed within the item
Usage Notes
· Use a When-Mouse-Down trigger to perform an action every time the operator presses down the mouse button within an item and/or canvas.
Note: The mouse down event is always followed by a mouse up event.
4. When-Mouse-Enter Trigger
Description
Fires when the mouse enters an item or canvas if one of the following events occurs:
· if attached to the form, when the mouse enters any canvas or item in the form
· if attached to a block, when the mouse enters any item in the block
· if attached to an item, when the mouse enters the item
Usage Notes
Use a When-Mouse-Enter trigger to perform an action every time the mouse enters an item or
canvas.
Do not use the When-Mouse-Enter trigger on a canvas that is larger than the window. Iconic
buttons and items on the canvas below the initial window cannot be selected. The user is able to scroll the canvas to see the items. However, as soon as the mouse enters that area, the trigger fires and returns focus to the previous target, so the user is never able to click on those items. Changing a tooltip's property in a When-Mouse-Enter trigger cancels the tooltip before it is ever shown.
Be careful when calling a modal window from a When-Mouse-Enter trigger. Doing so may
cause the modal window to appear unnecessarily.
For example, assume that your When-Mouse-Enter trigger causes Alert_One to appear
whenever the mouse enters Canvas_One. Assume also that your application contains two
canvases, Canvas_One and Canvas_Two. Canvas_One and Canvas_Two do not overlap each
other, but appear side by side on the screen. Further, assume that Alert_One displays within
Canvas_Two's border.
Finally, assume that the mouse has entered Canvas_One causing the When-Mouse-Enter trigger to fire which in turn causes Alert_One to appear.
When the operator dismisses the message box, Alert_One will appear again unnecessarily if the operator subsequently enters Canvas_One with the mouse. In addition, when the operator
moves the mouse out of Canvas_Two, any When-Mouse-Leave triggers associated with this
event will fire. This may not be the desired behavior.
begin
:GLOBAL.save_item_name := :SYSTEM.CURSOR_ITEM;
GO_ITEM(’s_ord.id’);
SHOW_VIEW(’cv_help’);
End;
WHEN-MOUSE-ENTER at Form Level
begin
IF :SYSTEM.MOUSE_CANVAS = ’CV_ORDER’ THEN
:control.show_help_button := ’?’;
END IF;
End;
5. When-Mouse-Leave Trigger
Description
Fires after the mouse leave an item or canvas if one of the following events occurs:
· if attached to the form, when the mouse leaves any canvas or item in the form
· if attached to a block, when the mouse leaves any item in the block
· if attached to an item, when the mouse leaves the item
Usage Notes
Use a When-Mouse-Leave trigger to perform an action every time the mouse leaves an item and/or canvas.
WHEN-MOUSE-LEAVE trigger on control.show_help_button
begin
HIDE_VIEW(’cv_help’);
GO_ITEM(:GLOBAL.save_item_name);
End;
6. When-Mouse-Move Trigger
Description
Fires each time the mouse moves if one of the following events occurs:
· if attached to the form, when the mouse moves within any canvas or item in the form
· if attached to a block, when the mouse moves within any item in the block
· if attached to an item, when the mouse moves within the item
Usage Notes
· Use the When-Mouse-Move trigger to perform an action every time the operator moves the mouse.
The When-Mouse-Move trigger may have performance implications because of the number of
times this trigger can potentially fire.
7. When-Mouse-Up Trigger
Description
Fires each time the operator presses down and releases the mouse button if one of the
following events occurs:
· if attached to the form, when the mouse up event is received within any canvas or item in a form
· if attached to a block, when the mouse up event is received within any item in a block
· if attached to an item, when the mouse up event is received within an item
Two events must occur before a When-Mouse-Up trigger will fire:
· Mouse down
· Mouse up
Usage Notes
Use the When-Mouse-Up trigger to perform an action every time the operator presses and
releases the mouse.
The mouse up event is always associated with the item that received the mouse down event.
For example, assume that there is a When-Mouse-Up trigger attached to Item_One. If the
operator presses down the mouse on Item_One, but then releases the mouse on Item_Two, the mouse up trigger will fire for Item_One, rather than for Item_Two.
K.OTHER TRIGGERS
1. User-Named Trigger
A user–named trigger is a trigger that you define yourself in a form, and then call explicitly from other triggers or user–named subprograms. Each user–named trigger defined at the same definition level must have a unique name.
To execute a user–named trigger, you must call the EXECUTE_TRIGGER built–in procedure, as shown here:
Execute_Trigger(’my_user_named_trigger’);
Note: You can write user–named PL/SQL subprograms to perform almost any task for which you might use a user–named trigger.
Usage Notes
User-named PL/SQL subprograms can be written to perform almost any task for which one
might use a user-named trigger.
As with all triggers, the scope of a user-named trigger is the definition level and below. When
more than one user-named trigger has the same name, the trigger defined at the lowest level has precedence.
It is most practical to define user-named triggers at the form level.
Create a user-named trigger to execute user-named subprograms defined in a form document
from menu PL/SQL commands and user-named subprograms. (User-named subprograms
defined in a form cannot be called directly from menu PL/SQL, which is defined in a different
document.) In the menu PL/SQL, call the EXECUTE_TRIGGER built-in to execute a usernamed
trigger, which in turn calls the user-named subprogram defined in the current form.
DO_KEY built-in
Executes the key trigger that corresponds to the specified built-in subprogram. If no such key
trigger exists, then the specified subprogram executes. This behavior is analogous to pressing
the corresponding function key.
Syntax
PROCEDURE DO_KEY
(built-in_subprogram_name VARCHAR2);
DO_KEY restrictions
DO_KEY accepts built-in names only, not key names: DO_KEY(ENTER_QUERY). To accept a
specific key name, use the EXECUTE_TRIGGER built-in: EXECUTE_TRIGGER('KEY_F11').
DO_KEY examples
/* ** Built-in: DO_KEY
** Example: Simulate pressing the [Execute Query] key. */
BEGIN
Do_Key('Execute_Query');
END;
2. On-Savepoint Trigger
Fires when Form Builder would normally issue a Savepoint statement. By default, Form Builder issues savepoints at form startup, and at the start of each Post and Commit Transaction process.
Usage Notes
To perform default Form Builder processing from this trigger, include a call to the
ISSUE_SAVEPOINT built-in.
In an On-Savepoint trigger, the Savepoint_Name application property returns the name of the
next savepoint that Form Builder would issue by default, if no On-Savepoint trigger were
present. In an On-Rollback trigger , Savepoint_Name returns the name of the savepoint to
which Form Builder would roll back.
Suppress default savepoint processing by setting the Savepoint Mode form document property
to Off. When Savepoint Mode is Off, Form Builder does not issue savepoints and,consequently, the On-Savepoint trigger never fires.
/* ** Built-in: ISSUE_SAVEPOINT
** Example: Perform Form Builder standard savepoint processing.
** Decide whether to use this built-in based on a global flag setup at startup by the form,perhaps based on a parameter.
** Trigger: On-Savepoint */
DECLARE
sp_name VARCHAR2(80);
BEGIN
/* Get the name of the savepoint Form Builder needs to issue */
sp_name := Get_Application_Property(SAVEPOINT_NAME);
/* Check the global flag we setup at form startup */
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_savept name='||sp_name);
/* Otherwise, do the right thing. */
ELSE
Issue_Savepoint(sp_name);
END IF;
END;
3. Post-Change Trigger
Fires when any of the following conditions exist:
· The Validate the Item process determines that an item is marked as Changed and is not NULL.
· An operator returns a value into an item by making a selection from a list of values, and the item is not NULL.
· Form Builder fetches a non-NULL value into an item. In this case, the When-Validate-Item trigger does not fire. If you want to circumvent this situation and effectively get rid of the Post-Change trigger, you must include a Post-Query trigger
in addition to your When-Validate-Item trigger. See "Usage Notes" below.
Usage Notes
· The Post-Change trigger is included only for compatibility with previous versions of Form Builder. Its use is not recommended in new applications.
· The Post-Query trigger does not have the restrictions of the Post-Change trigger. You can use Post-Query to make changes to the fetched database values. Given such changes, Form Builder marks the corresponding items and records as changed.
3. On-Lock Trigger
Fires whenever Oracle Forms would normally attempt to lock a row, such as when an operator presses a key to modify data in an item. The trigger fires between the key press and the display of the modified data.
Usage Notes:
· Use an On–Lock trigger to replace the default Oracle Forms processing for locking rows. For example, if you are designing an application for use on a single–user system, you can use the On–Lock trigger to speed processing by bypassing all lock processing. Also, use On–Lock if you are accessing a non–ORACLE data source directly, not by way of Open Gateway.
· When the On–Lock trigger fires as a result of an operator trying to modify data, the trigger fires only the first time the operator tries to modify an item in the record. The trigger does not fire during subsequent modifications to items in the same record. In other words, for every row that is to be locked, the trigger fires once.
· To perform the default Oracle Forms processing from this trigger, include a call to the LOCK_RECORD built–in.
· Use this trigger to lock underlying tables for non–updateable views.
/* ** Built-in: LOCK_RECORD
** Example: Perform Form Builder standard record locking on the queried record which has just been deleted or updated. Decide whether to use default processing or a user exit by consulting a global flag setup at startup by the form,perhaps based on a parameter.
** Trigger: On-Lock */
BEGIN
/* ** Check the global flag we set up at form startup */
IF :Global.Non_Oracle_Datasource = 'TRUE' THEN
User_Exit('my_lockrec block=EMP');
/* ** Otherwise, do the right thing. */
ELSE
Lock_Record;
END IF;
END;
5. Pre-Popup-Menu Trigger
This trigger is called when a user causes a pop-up menu to be displayed. (In a Microsoft Windows environment, this occurs when a user presses the right mouse button.) Actions defined for this trigger are performed before the pop-up menu is displayed.
Usage Notes
Use this trigger to enable or disable menu items on a pop-up menu before it is displayed.
6. Query-Procedure Trigger
Automatically created by Form Builder when the query data source is a stored procedure. This trigger is called when a query operation is necessary. Think of this as an On-Query trigger that is called by the system instead of doing default query operations.
Usage Notes
When constructing a query, any of the items may be used, but the Query Data Source Columns property must be set so that those items can be passed to the query stored procedure. Then, the query stored procedure has to use those values to filter the data. This means that the enter query mode does not happen automatically unless you specify it.
7. Update-Procedure Trigger
Automatically created by Form Builder when the update data source is a stored procedure. This trigger is called when a update operation is necessary. Think of this as an On-Update trigger that is called by the system instead of doing default update operations.
8. When-Custom-Item-Event Trigger
Fires whenever a VBX control sends an event to Oracle Forms.
Usage Notes:
Use a When–Custom–Item–Event trigger to respond to a selection or change of value for a VBX control. The system variable SYSTEM.CUSTOM_ITEM_EVENT stores the case–sensitive name of the event that occurred, and the system variable
SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS stores a parameter name that contains the supplementary arguments for an event that is fired by a VBX control.
Example:
This is an example of a procedure that can be called when Oracle Forms fires the When–Custom–Item–Event Trigger.
DECLARE
TabEvent varchar2(80);
TabNumber Number;
BEGIN
TabEvent := :system.custom_item_event;
/* ** After detecting a Click event, identify the ** tab selected, and use the user–defined Goto_Tab_Page procedure to navigate to the selected page. */
IF (UPPER(TabEvent) = ’CLICK’) THEN
TabNumber := VBX.Get_Property(’TABCONTROL’,’CurrTab’);
Goto_Tab_Page(TabNumber);
END IF;
END;
9. When-Form-Navigate Trigger
Fires whenever any peer form navigation takes place. form
Use a When–Form–Navigate trigger to perform actions when any cross form navigation takes place without relying on window activate and window deactivate events.
This is an example of a procedure that can be called when Oracle Forms fires the When–Form–Navigate Trigger.
DECLARE
win_id WINDOW := FIND_WINDOW(’WINDOW12’);
BEGIN
if (GET_WINDOW_PROPERTY(win_id,WINDOW_STATE) = ’MAXIMIZE’ THEN
SET_WINDOW_PROPERTY(win_id,WINDOW_STATE,MINIMIZE);
else
SET_WINDOW_PROPERTY(win_id,WINDOW_STATE,MAXIMIZE);
end if;
END;
10. When-Tab-Page-Changed
Fires whenever there is explicit item or mouse navigation from one tab page to another in a tab canvas.
Usage Notes
· Use a When-Tab-Page-Changed trigger to perform actions when any tab page is changed during item or mouse navigation.
· When-Tab-Page-Changed fires only when tab page navigation is explicit; it does not respond to implicit navigation. For example, the trigger will fire when the mouse or keyboard is used to navigate between tab pages, but the trigger will not fire if an end user presses [Next Item] (Tab) to navigate from one field to another field in the same block, but on different tab pages.
· When-Tab-Page-Changed does not fire when the tab page is changed programmatically.
Example
/* Use a When-Tab-Page-Changed trigger to dynamically change a tab page's label from lower- to upper-case (to indicate to end users if they already have ** navigated to the tab page): */
DECLARE
tp_nm VARCHAR2(30);
tp_id TAB_PAGE;
tp_lb VARCHAR2(30);
BEGIN
tp_nm := GET_CANVAS_PROPERTY('emp_cvs', topmost_tab_page);
tp_id := FIND_TAB_PAGE(tp_nm);
tp_lb := GET_TAB_PAGE_PROPERTY(tp_id, label);
IF tp_lb LIKE 'Sa%' THEN
SET_TAB_PAGE_PROPERTY(tp_id, label, 'SALARY');
ELSIF tp_lb LIKE 'Va%' THEN
SET_TAB_PAGE_PROPERTY(tp_id, label, 'VACATION');
ELSE null;
END IF;
END;
11. When-Tree-Node-Activated Trigger
Fires when an operator double-clicks a node or presses Enter when a node is selected.
Usage Notes
· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE returns a value of type NODE.
· No programmatic action will cause the When-Tree-Node-Activated trigger to fire. Only end-user action will generate an event.
12. When-Tree-Node-Expanded Trigger
Fires when a node is expanded or collapsed.
Usage Notes
· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE returns a value of type NODE.
· No programmatic action will cause the When-Tree-Node-Expanded trigger to fire. Only end-user action will generate an event.
13. When-Tree-Node-Selected Trigger
Fires when a node is selected or deselected.
Usage Notes
· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE returns a value of type NODE.
· No programmatic action will cause the When-Tree-Node-Selected trigger to fire. Only end-user action will generate an event.
Defining list items
A list item displays a predefined set of choices that
are mutually exclusive
can be displayed as either a poplist, text list, or combo box
List item
description
Poplist
Appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.
Text List
Appears as a rectangular box which displays a fixed number of values. When the text list contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the operator to view and select undisplayed values.
Combo Box
Combines the features found in list and text items. Unlike the poplist or the text list style list items, the combo box style list item will display fixed values and accept one operator-entered value.
The combo box list item appears as an empty box with an icon to the right. The user can enter text directly into the combo field or click the list icon to display a list of available values.
No comments:
Post a Comment