Previous | Table of Contents | Next

Page 394


FIG. 15.12
The Object Navigator
and the program units
object.



    Clicking the Create button brings up the New Program Unit dialog box, which records the name of the program unit and one of four types: procedure, function, package specification, and package body. The types are explained in Chapter 10, "Using SQL *LOADER, and Chapter 11, "Using an Oracle Form."

  1. Populate the New Program Unit dialog box with the name of the program unit and the type of program unit. Press the OK button when done. Figure 15.13 shows the box.

Fig. 15.13
The New Program Unit
dialog box ready to
create a procedure
called
Form_Security_Checks.


    After the OK button is clicked , Designer opens the PL/SQL editor for the procedure. It will contain an empty PL/SQL structure.

  1. Enter the PL/SQL for the trigger into the structure.

  2. Perform a syntax check by pressing the Compile button.

Figure 15.14 shows the Form_Security_Checks procedure, which has the same PL/SQL statements used in the Pre-insert trigger defined earlier.

To use this procedure in any of the security triggers, place the name of the procedure in trigger code. Figure 15.15 shows the Pre-delete security trigger using the security procedure.

Page 395


FIG. 15.14
The
Form_Security_Checks
procedure.




FIG. 15.15
The Pre-delete trigger
using the
Form_Security_Checks
procedure.


Adding and Populating Non-Base Table Text Items

Relational tables often contain foreign key columns that relate one table to another table. These related tables usually contain descriptive information about the entity. For example, the Department table contains the name of the department. The department name provides more description about the employee than the value of the fk_department column on the Employee table.

These descriptive fields enhance the presentation of a form. In order to get the fields on the form, it is possible to create a view joining the tables. The view is then used as the base table name for the block. The descriptive fields will be populated as the result of normal form operations. There are two problems with this technique. A form cannot perform insert, update, or

Page 396

delete operations against a view. The second problem is performance. Each time a query that contains a join is executed, Oracle creates a new virtual table. When the database contains a large amount of records, this could cause performance problems.

The best way to populate these form description fields is to fire Post-query or post-change triggers that populate the fields, which allows the block to use an actual table name. Database transactions can be performed and the Oracle will not have to process a table join when retrieving records to the form.

To illustrate this technique, the Employee Update form is modified and the employee's department name placed on the form. This enhances the employee information on the form. The user will not have to interpret the department value to determine the name of the employee's department.

The following are the steps to place a department description field on the Employee Update form.

  1. The first step is to create a new text item on block one and place it on the form. The best spot for the department name is on the second row below the department, which means the social security number item must be moved.
  2. Create a new text item by using the button tool on the Layout Editor, or by copying and pasting an existing item on the Layout Editor, or by selecting a text item object on the Object Navigator and clicking the create icon.
NOTE
I prefer the copy/paste method.n
  1. Select the street field.
  2. Select the Edit, Copy menu option.
  3. Select the Edit, Past-menu option.
  4. Drag the new item to the appropriate spot. Figure 15.16 shows the Layout Editor after the new text item (item12) has been positioned.
  5. The next step is to change the item's properties. Double-click the item in the Layout Editor. Update the properties to the following:
Name Department_name
Base Table False
Maximum Length 30
Navigable False
Query Allowed False
Insert Allowed False
Update Allowed False

Page 397

FIG. 15.16
The Employee Update
form after a new text
item that will contain
the department name
has been added.


Changing the base table property makes this field a nonbase-table text item. This means the item will not be populated through the normal Forms Runtime query process. The text item will be populated by using a block-level Post-query trigger.

The Post-query trigger executes a SELECT statement after the Employee record is fetched to the form. The select statement retrieves the department name from the Department table based upon the value contained in the fk_department field on block one. The trigger is shown in Figure 15.17. The upper and rtrim functions are used in the where clause to ensure that any case sensitivity problems are eliminated.


Fig. 15.17
The Post-query trigger
that will populate the
department name field.


Page 398

NOTE
When using a Post-query trigger, you must include an exception handler. I feel it looks professional to place a value in description fields when the search value is Null or does not exist in the queried table. More importantly, if the search value does not exist in the master table, a no-record-found error occurs. Forms Runtime issues the error message for each record returned by the main query. This will cause your users a great deal of trouble and probably get you some phone calls.n

Figure 15.18 displays the completed form. The employee record now contains the department name.

FIG. 15.18
The Employee Update
form displaying the
department name in a
non-base table item.


When placing new items on a form or rearranging the text items, the tab order of the items is generally disrupted, and the tab order of the items is changed on the Object Navigator. To reset the tab order, expand the block so that all of the items can be seen. The tab order of the items is the order of the items under the block. Select and drag the items to their proper sequence.

TIP
You can select objects on the Object Navigator and move them between different blocks on a form and even between different modules. If you place an object on the wrong block or want to copy an object from another form, drag the object on the Navigator.

Using Alerts

An alert is a modal dialog box that can be launched by a trigger and is used to get the operator's attention. Thus far, we have seen a number of triggers that issue passive messages. Passive messages are placed on the message or status line and are easy for the operator to miss. The alert is displayed within the window and requires a clicking action by the operator. Alerts are very difficult for an operator to miss or ignore.

Previous | Table of Contents | Next