|
"Go confidently in
the direction of your dreams. Live the life you have imagined."
Henry David Thoreau (1817 - 1862) |
Read
first then play the video:
FRM-VIDEO -(Hands-On
06) How to use FORMS Triggers (POST-QUERY)
Triggers
Introduction
Forms Trigger
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 and POST-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.
PRE-INSERT and
WHEN-NEW-FORM-INSTANCE trigger
Some other useful triggers
are: PRE-INSERT and WHEN-NEW-FORM-INSTANCE.
A PRE-INSERT
trigger fires once before each new record is inserted in a commit
process. The "WHEN-NEW-FORM-INSTANCE" trigger will be used
to prepare objects or an action when entering to a new form. It fires
when the form is entered.
Hands-On
Now, the user"s DBA
removed the "itemtot" column whose content was depended on the
other columns. Your client wants you to remove the "itemtot" item
from layout screen.
Also, your application only
shows products' ID. Your client wants to see product's description
since product's ID does not tell them what the product is.
They want you to remove
duplicate item information from "Items" tab canvas; and add
a new item to display product's description in the "Items"
tab canvas.
See Figure 8.
Your tasks are:
1- Remove duplicate item
information from "Items" tab canvas.
2- Add a new item to
display product's description "Product Description" in the
"Items" tab canvas.
3- Run and test all user
functional requirements.
F In this Hands-On,
you will learn how to: use the post-query trigger, Compile Triggers,
use tab canvas, use "object navigator," use "Data Blocks," use
"Layout Editor," use "Property Palette," use "Run Form,"
and "Execute Query."
Figure 8
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_V05)
in the "iself" folder.
Save a Module
Click on the "CUSTOMER_ORDERS_V05"
form. The color changes to blue. Change it and then save the Form name
as version 06 (customer_orders_v06). This way the original form
is untouched.
Layout Editor
In the Main menu, choose
the "Tools" sub-menu and select the Layout Editor option.
Adjust the window, if you
need it. In the Layout Editor window, select the "ITEMTOT"
item by clicking on it and press the "Delete" function key
to remove the object since it is duplicated.
Make some window adjustment
to make space for the product description.
Add a Display Item
In the toolbar in the
Layout Editor window, select the "Display Item" icon. Drag the "+"
sign on the canvas and click it where you wish to add your Product
Description item (DISPLAY_ITEMnn). Adjust its size.
Make sure the BLOCK box in
the Layout Editor shows "ITEM."
Change Property Palette
sheets
Right click on the new item
and open its property palette. Change name to "PRODUCT_DESC."
Change "database item" to "no." On "Prompt"
type "Product Description." Change "Prompt Attachment edge" to
" Top." Then close the window.
Do some window adjustments
if needed.
Notice that the
"Product Description" is not in the item table, therefore
you need to create a trigger to query that information.
Create a Trigger
Expand the ITEM data block.
Notice that the box next to Trigger is empty. That means the ITEM data
block does not have any trigger.
Highlight the Trigger item
and click on the green "+" sign on the toolbar in the Object
Navigator window.
POST-QUERY Trigger
In the Trigger window, type
a letter "P" and you will see all the triggers that start with the
letter "P." Select the "POST-QUERY" trigger.
PL/SQL editor
In the PL/SQL Editor
window, write a query to move "product description" into the
display item for each "prodid" on the screen.
(PL/SQL Editor)
SELECT descrip INTO :product_desc
FROM product
WHERE prodid = :prodid;
Compile a PL/SQL statement
Compile it.
You should get successfully
compiled.
Close the window.
Navigate through the
application layout.
Run the Form Runtime
Then run the application.
Execute Query
Execute the query and then
navigate through the application.
Check the changes. Test the
"product description."
Check if the description is
right.
Close the window and save
all the changes.
|
"Human beings, by
changing the inner attitudes of their minds, can change the
outer aspects of their lives." William James (1842 - 1910) |
Questions:
Q: What is a Forms Trigger?
Q: Describe the
WHEN-BUTTON-PRESSED trigger.
Q: Describe the PRE-QUERY
trigger.
Q: Describe the POST-QUERY
trigger.
Q: Describe the PRE-INSERT
trigger.
Q: Describe the
WHEN-NEW-FORM-INSTANCE trigger.
Q: Now, the user"s DBA
removed the "itemtot" column whose content was depended on the
other columns. Your client wants you to remove the "itemtot" item
from layout screen.
Also, your application only
shows products' ID. Your client wants to see product's description
since product's ID does not tell them what the product is.
They want you to remove
duplicate item information from "Items" tab canvas; and add
a new item to display product's description in the "Items"
tab canvas.
See Figure 8.
Your tasks are:
1- Remove duplicate item
information from "Items" tab canvas.
2- Add a new item to
display product's description "Product Description" in the
"Items" tab canvas.
3- Run and test all user
functional requirements.
|