Page 394
FIG. 15.12
The Object Navigator
and the program units
object.
Fig. 15.13
The New Program Unit
dialog box ready to
create a procedure
called
Form_Security_Checks.
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.
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.
NOTE |
I prefer the copy/paste method.n |
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. |
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.