08/23 FORMS - 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.

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.

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.