Previous | Table of Contents | Next

Page 384

Trigger Levels

Triggers can exist on three levels: form, block, and item. Form-level triggers execute when the event occurs anywhere on the form. Block-level triggers fire when the event happens on the block. Item-level triggers fire as a result of item events.

Some triggers are unique to the level, but many of the triggers can be placed on any of the levels. For instance, a Post-query trigger cannot be placed on an item; it is usually placed as a block-level trigger. The Key-exeqry trigger, which is fired when the execute-query function key is clicked, can be placed on all three levels. A lower-level trigger overrides triggers defined at a higher level. This can cause unexpected results when the same trigger is used on different levels. Triggers should always be created on the lowest possible level.

Creating a Trigger

The following steps can be used to create a trigger:

  1. Open the Object Navigator and select the form object linked to the trigger. Expand the object. Select the triggers object. Click the Create icon on the tool palette. This opens the Triggers dialog box.

  2. Select the appropriate trigger from the Trigger dialog box. Click OK. This opens the PL/SQL editor.

  3. Enter the PL/SQL statements in the editor. Click the Compile button to validate the statements. Click the Close button when done.

As an example of the preceding procedure, the following illustrates the creation of a When-validate-item trigger that allows only a future date to be entered for the last_payment_date on block two. When an invalid date is entered, the form processing is terminated and an error message placed on the message line.

  1. The first step is to create a trigger object. Open the Object Navigator.

  2. Select and expand block two.

  3. Select and expand the items object.

  4. Select and expand the last_payment_date item. The triggers object will be visible.

Figure 15.1 displays the Object Navigator after these steps have been performed.

To open the Triggers dialog box, select the trigger object and click the Create icon on the left side of the navigator.

This opens the Triggers dialog box, as shown in Figure 15.2. The dialog box is a list box that can be used to identify and select a trigger. The scroll bar on the right side of the window may be used to scroll through the triggers. At the top of the box is a window item to place characters that can be used in a search. The find button executes the search by using the characters. Figure 15.2 shows a search for triggers that begin with a "W" and have a "V" in the expression.

To open the PS/SQL editor, select the When-Validate-Item trigger and click the OK button.

Page 385

FIG. 15.1
The Object Navigator
ready for the trigger to
be created on the
last_payment_date
item.




FIG. 15.2
The Triggers dialog box
that displays the results
of a character string
search.


This opens the PL/SQL editor displayed in Figure 15.3. This editor records the PL/SQL code that will be executed by the trigger. The PL/SQL block in the figure consists of a typical PL/SQL block. It consists of an if-then-else statement. If the date in the last_payment_date item on block two is less than the current date, the trigger will display an error message and terminate processing. The "bell" keyword is a built-in subprogram that causes a sound alerting the user to the error condition. Processing is terminated by the "raise form_trigger_failure" command. Enter the PL/SQL script contained in Figure 15.3 into the When-Validate-Item trigger.

NOTE
PL/SQL code used in a trigger must have a colon precede any non-database variable used by the code. In Figure 15.3, the variable :two.last_payment_date is a form item. It must have the colon precede the name. In addition, assignment operators must also use the colon. This last item is standard PL/SQL symbology, but an error I make consistently.n

When done placing the code, click the Compile button to validate the script. The code in figure 15.3 is correct and will compile successfully. In Figure 15.4, the colon in front of the :two.last_payment_date item was removed. The figure illustrates the error message displayed when faulty PL/SQL code is compiled.

After the PL/SQL is validated, press the Close button. The trigger can now be used. Generate and run the form. Figure 15.5 displays the executed Employee Update form after the trigger detects an error during the validation process.

Page 386


FIG. 15.3
The When-Validate-Item
trigger PL/SQL code
block.




FIG. 15.4
The PL/SQL code
containing an error in
the syntax of the
two.last_payment_date.




Using the PL/SQL Editor

The PL/SQL editor records PL/SQL statements in the form. Selecting the Tools/PL/SQL menu option or double-clicking a PL/SQL item on the Object Navigator opens it. The editor has six buttons near the top of the box, as follows:

Button Description
Compile The button on the far left. It compiles and syntax checks the PL/SQL statements currently in the editor.
Revert The next button, which returns the statements in the editor to the contents of the editor when last saved. This button allows the developer to undo any problems the modifications may have caused.

Page 387

Button Description New Launches the Triggers dialog box, from which the developer can specify a type of trigger. Designer then clears the contents of the editor, allowing the developer to begin development of a new trigger. Delete Removes the PL/SQL object from the form. Close Closes the current window. Help Launches the Designer Help facility.

FIG. 15.5
The Employee Listing
displaying the error
message produced by
the When-Validate-Item
trigger.


Following the buttons are several picklists that locate PL/SQL objects. Type picklist contains two types of objects: program units and triggers. Selecting "program unit" causes the Name picklist to contain the name of the form's program units. The Object boxes are grayed out because they do not apply to program units.

Selecting the Triggers value in the Type picklist causes the Name picklist to display the triggers available on the form. The Object picklists are active for this type of object. The first Object picklist has a value called "(Form Level)" and lists values for each of the form's block. When this picklist contains a value of (Form Level), the Name picklist displays all of the form's triggers. When a particular block is selected, the Name picklist displays the triggers for that block. This activates the Object Items picklist, which contains the name of each of the block's items. It can be used to select an item and further limit the trigger contained on the Name picklist. Figure 15.6 displays the PL/SQL editor with the Object Items picklist displaying the items for block one.

Using Built-In Subprograms

Oracle Forms has developed programs that control the behavior of a form. They provide a myriad of functions such as moving the cursor from one item to another, navigating from a

Previous | Table of Contents | Next