Trigger (PRE-QUERY), Set
dynamically properties
Introduction
A forms trigger is a block of PL/SQL code that adds functionality to
your application. Triggers are attached to objects in your
application. When a trigger is fired, it executes the code it
contains. Each trigger�s name defines what event will fire it; for
instance, a WHEN-BUTTON-PRESSED trigger executes its code each time
you click on the button to which the trigger is attached. Or, we can
say, a forms trigger is a set of PL/SQL actions that happen each
time an event such as when-checkbox-changed, when-button-pressed, or
when-new-record-instance occurs. You can attach several triggers to
a data query. The most popular of them are the PRE-QUERY and
POST-QUERY.
PRE-QUERY trigger
The PRE-QUERY trigger fires before the select statement is
finalized. The POST-QUERY trigger fires before selected records are
presented to the user. It fires after records are retrieved but
before they are displayed. So, you can use it to enhance a query�s
records in a number of ways. Your Post-Query trigger can contain
code to calculate or populate control items.
Change an object property dynamically
You can also change an object property dynamically. The
�SET_objectname_PROPERTY� built-in subprogram will change an object
property dynamically.
Hands-On
On the "Orders" tab, your client wants to see the most recent or
last customer orders. They ask you to sort the customer orders list
based on their order�s date. They like to have two radio buttons to
allow them to manipulate the order of displayed records by order
date.
You have been assigned to create two radio buttons, Labeled
"Ascending" and
"Descending" to sort displayed record order by "order date."
See Figure 11.
Your tasks are:
1- Create a Radio Group.
2- Create two Radio Buttons associated with step 1 (Radio Group).
3- Label the first Radio Button �Ascending.�
4- Label the second Radio Button �Descending.�
5- Run and test all user functional requirements.
You will learn how to: create and use PRE-QUERY trigger, set
dynamically block property on a table's data block and execute the
query using trigger, use tab canvas, use �object navigator,� use
�Data Blocks,� use �Layout Editor,� use �Property Palette,� use �Run
Form,� and �Execute Query.�
|
Figure 11 |
Open a Module
In the �Object Navigator� window, highlight Forms. Go to the Main
menu and choose �File,� select �Open� to open an existing form
(customer_orders_V08)
from the �iself� folder.
Save a Module
Click on the �CUSTOMER_ORDERS_V08� form. The color will change to
blue. Now, you should be able to change the name and then save the
Form name as version 09 (customer_orders_v09). This way the original
form is untouched.
Open Layout Editor
In the Main menu, choose the �Tools� sub-menu and select the �Layout
Editor� option. In the Layout Editor window, make sure that the
Canvas box contains your tab canvas. If not, click on the down arrow
next to its box and select it and also make sure in the Layout
Editor window, the BLOCK box contains the ORD block.
Create a Control Data Block
In the Object Navigator window, highlight the Data Blocks item and
click on the green �+� sign to create a data block. In the �New Data
Block� window, select the �Build a new data block manually� option
and then click �OK.�
Change a property palette sheets
Now an object was created (BLOCKnn). Right click on the object and
open its Property Palette. Change "name" to sort_block. And press
the enter key. Change "Database Data Block" to "no." Close the
window.
Create a Radio Group and Radio Button
In the Layout Editor, on the �BLOCK� box, select the sort_block item
and click on the Radio Button icon to create a radio button. Drag
the �+� sign on the canvas and click where you wish to have your
Radio Button. In the �Radio Group� window, click �new� to create a
new �Radio Group� for sorting.
Notice on the "Object Navigator," a radio group and radio button
were created.
In the Object Navigator, right click on the new RADIO_GROUPnn item
and open its Property Palette.
In its Property Palette, change the "name" property to SORT_SELECT
or any name you like and then press the enter key. Change "initial
Value" to "orderdate asc." Then press the enter key and click �OK.�
Change "Database Item" to "no." Close the window.
Right click on the RADIO_BUTTONnn item and open its property
palette. Change the "name" property to RB_SORT_ASC. The RB stands
for Radio Button. Change "radio button value" to "orderdate asc."
Change "Label" to "Ascending." Close the window.
Copy and Paste a Radio Button
Copy and paste the Radio Button item. Make sure to place it in the
�sort_select� Radio Group. Then click �OK.�
Move an object
Move the new "radio button" item similar to user requirement with
above figure.
Right click on it and open its Property Palette.
Change a property palette sheets
In the Property Palette window, change the NAME to RB_SORT_DESC.
Change the "label� to Descending. Change "Radio Button Value" to "orderdate
desc." Then close the window.
Change an appearance of an object
Click on the "Text" icon; and type 'Sort Order Date.' Then click
outside of the text box.
In the Layout Editor, you can change the appearance of the text by
clicking on the B, I, U icons on the top horizontal toolbar.
Create a Trigger
In the Object Navigator, highlight the Trigger of the �SORT_SELECT�
radio group and click on the green �+� sign to create a trigger.
WHEN-RADIO-CHANGED� trigger
In the Triggers window, type a letter �W� then �R.� Select the
�WHEN-RADIO-CHANGED� trigger and click �OK.�
PL/SQL Editor
In the PL/SQL Editor, write a PL/SQL procedure to go to the order
block (ORD) and then execute the query statement.
(PL/SQL Editor)
GO_BLOCK(ord);
EXECUTE_QUERY;
Compile a trigger
Compile the trigger and close the window.
Create a trigger
In the Object Navigator, highlight the Trigger of the �ORD� data
blocks and click on the green �+� sign to create a trigger
PRE-QUERY trigger
In the Triggers window, type a letter �P� then �Q.� Select the
�PRE-QUERY� trigger and click �OK.�
PL/SQL Editor
In the PL/SQL Editor, write a PL/SQL procedure to change the
�order_by� property of the �ORD� data block. Notice that the
�sort_select� value is changed based on the radio button you select.
(PL/SQL Editor)
SET_BLOCK_PROPERTY (�ord�, ORDER_BY, :sort_block.sort_select);
Compile Trigger
Compile the trigger. Close the window.
Run the Form
Run the application.
Execute Query
Click �Execute query.�
Navigate
Navigate through the application and check the sort radio button.
After the testing is done, close the window and save the changes.
Questions:
Q: Describe the PRE-QUERY trigger.
Q: How do you set a block property dynamically?
Q: Can you attach several triggers to an object in the Form Module?
Q: On the "Orders" tab, your client wants to see the most recent or
last customer orders. They ask you to sort the customer orders list
based on their order�s date. They like to have two radio buttons to
allow them to manipulate the order of displayed records by order
date.
You have been assigned to create two radio buttons, Labeled
"Ascending" and
"Descending" to sort displayed record order by "order date."
See Figure 11.
Your tasks are:
1- Create a Radio Group.
2- Create two Radio Buttons associated with step 1 (Radio Group).
3- Label the first Radio Button �Ascending.�
4- Label the second Radio Button �Descending.�
5- Run and test all user functional requirements. |
