A Comprehensive Website for Oracle Learning

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.

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.

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, your client desires it would be better if they could get specific history of product price rather than entire product history prices table. They want you to add more functionality to their application to display only current history of product prices information.

Note: The current product would be defined by where the position of your cursor is.

You have been assigned to modify their application to query only history of product price information for a product rather than entire table.

See Figure 14.

Your tasks are:

1- Automate the �product history prices� application to query only a history of product prices.

2- Pass product ID as a parameter between these two Form applications.

3- Run and test all user functional requirements.

FYou will learn how to: use and assign global variable, pass global variable from one Form to another, use global variable to execute query base on its parameter, create and use the "WHEN-NEW-FORM-INSTANCE" trigger, use tab canvas, use �object navigator,� use �Data Blocks,� use �Layout Editor,� use �Property Palette,� use �Run Form,� and �Execute Query.�

Figure 14

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_V11) in the folder.

Save Modules

Click on the �CUSTOMER_ORDERS_V11� form. The color changes to blue. Then change its name and save the Form name as version 12 (customer_orders_12). This way the original form is untouched.

Also, you should save the �Product History Prices Data Entry� (PROD_DE) Form name as version 2 (PROD_DE_V02).

Now, we want to change the application to query all the product history prices for an item that the cursor is on.

Temporary Holder

In the PROD_DE_V02 form, expand the form, highlight the Data Blocks item and then click on the green �+� sign to create a data block.

New Data Block

In the �New Data Block� window, choose the �Build a new data block manually� option and click "OK."

Change a property palette sheet

Now, you have a new data block (Blockn). Right click on it and open its Property Palette. In its property palette, change its name to CONTROL_BLOCK. Change "Database Data Block" to "NO." Close the window.

Create an Item

In CONTROL_BLOCK, create a new item. Open its property palette. Change the "name" item to �TEMP_VALUE.� Change its datatype to NUMBER with length 7 bytes. Change "database item" to �NO.� Close the window.

Copy Value from Item

Select �Prodid� on the price data block; and open its property palette. For �Copy Value from Item� type "control_block.temp_value." Close the window.

Create a trigger

In the PROD_DE_V02 form, create a "WHEN-NEW-FORM-INSTANCE" trigger at Form level.

WHEN-NEW-FORM-INSTANCE trigger

In the Trigger window, type �W� and �N� and select the WHEN-NEW-FORM-INSTANCE trigger.

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to move the global prodid item value into the temp_value item. Then go to the �price� data block and execute the query function.

(PL/SQL Editor)

:temp_value := :global.prodid;

GO_BLOCK (�price�);

EXECUTE_QUERY;

Compile a trigger

Compile the trigger and close the window.

Compile a module

Highlight the Form and save it. Then go to File > Administration > Compile file to compile it.

Create a Push Button

On the CUSTOMER_ORDERS_V12 Form, right click on the �Product History Data Entry� push button in the ITEM tap canvas to select �Smart Trigger� and select the "WHEN-BUTTON-PRESSED" trigger.

WHEN-BUTTON-PRESSED trigger

In the PL/SQL Editor, write a PL/SQL procedure to move the PRODID value into the global prodid item and call the Product History Price Data Entry form.

(PL/SQL Editor)

:GLOBAL.prodid := NAME_IN(�item.prodid�);

OPEN_FORM(�c:_de_v02�);

Compile the trigger and then close the window.

Run the Form

Run the application.

Execute Query

Click on the �Execute Query� icon.

Navigate the application

Navigate through the application.

Highlight a product id and then click on the �Product History Prices Data Entry� push button.

Now, you should only see information about that specific product id not all the products.

Repeat the process for some other items.

Then, close all the windows and save the changes.

Questions:

Q: How do you define a binding variable in a multiple forms?

Q: How do you assign a value to a global variable?

Q: How do you pass a global variable value from one form to another?

Q: How do you use a global variable to execute a query based on its parameter?

Q: Describe the "WHEN-NEW-FORM-INSTANCE" trigger.

Q: How do you create the "WHEN-NEW-FORM-INSTANCE" trigger?