Chapter 15

Working with Triggers

In this chapter, you will understand and demonstrate knowledge in the following areas:

• Introduction to form triggers
• How to produce triggers
• Adding functionality to form items
• Using query triggers
• Debugging triggers

Triggers are the bread and butter of your work creating a Form Builder application. You have already worked briefly with triggers in prior chapters of this book. Now it is time for a formal introduction. You will start by learning about trigger categories, types, scope, and properties. Next, you will learn how to produce triggers for a variety of different objects, utilizing trigger-specific components and subprograms. You will practice adding functionality to form items by attaching triggers to them, and see how you can exert greater control over query results by employing query triggers. Then, you will be learn how to use the form Debugger, which can provide major assistance when you need to track down misbehaving code. The contents of this chapter represents about 22 percent of OCP Exam 3 test content.

In this section, you will cover the following points related to form triggers:

• Definition of a trigger
• Form trigger categories
• Form trigger types and scope
• Form trigger properties

In this section you will learn about the numerous categories of triggers Form Builder offers; be introduced to the two form trigger types; learn how to control a trigger’s scope; and see all of a form trigger’s properties.

A 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 activated, or 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 the user clicks on the button to which the trigger is attached.

The triggers most commonly used in Form Builder fall into several functional categories. There are block-processing triggers such as ON-DELETE, interface event triggers like WHEN-BUTTON-PRESSED, master/detail triggers such as ON-POPULATE-DETAILS, message-handling triggers like ON-MESSAGE, navigational triggers such as WHEN-NEW-FORM-INSTANCE, query-time triggers like POST-QUERY, transactional triggers such as PRE-INSERT, and validation triggers like WHEN-VALIDATE-ITEM. Most of the triggers you deal with will fall into the category of interface event triggers. These fire as the user interacts with your application’s GUI. Each time the user clicks on a button, chooses from a list, or changes a radio group or check box, a series of triggers is available to control the application’s response.

Interface event triggers generally have names fitting the format WHEN-object-action. For instance, list triggers include WHEN-LIST-ACTIVATED and WHEN-LIST-CHANGED. For triggers that fire when a new instance of an object is created, the format WHEN-NEW-object-INSTANCE prevails. For instance, if you wanted to establish certain settings when a particular form opens, but did not want the application to bother reestablishing those settings just because the user navigated out of the form and then came back into it, the trigger WHEN-NEW-FORM-INSTANCE would do the trick. There are also quite a few triggers for mouse events, such as WHEN-MOUSE-CLICK, WHEN-MOUSE-DOUBLE-CLICK, WHEN-MOUSE-DOWN, WHEN-MOUSE-UP, WHEN-MOUSE-ENTER, and WHEN-MOUSE-LEAVE. In addition, there are over 40 triggers to respond to keystrokes; these use the naming format KEY-keytype. Examples include KEY-DOWN, KEY-UP, KEY-F1, KEY-ENTER, and a very useful addition, KEY-OTHERS. The KEY-OTHERS trigger fires whenever the user presses a key that can have a trigger but does not. It is an excellent way to disable unwanted function keys, or to perform one or more actions each time the user presses any key.

Triggers can also be divided according to their trigger type and trigger scope. There are two types of triggers: built-in and user-named. Built-in triggers correspond to specific runtime events and are supplied with Form Builder. User-named triggers are not provided with Form Builder; they are written by developers like you, and their names can be whatever you, the developer, desire. User-named triggers are required only in special situations, and they can only be run using the EXECUTE_TRIGGER built-in procedure from within a user-named subprogram, built-in trigger, or menu item command.

A trigger’s scope defines what event must occur in order for the trigger to fire as a result. The trigger scope is usually determined by the object to which it is attached: its scope encompasses the object itself, and any smaller objects contained therein. For instance, if you defined a WHEN-NEW-ITEM-INSTANCE trigger for a single item on a canvas, the trigger would fire whenever the user navigated to that item. If you moved the trigger to a block, it would fire each time the user navigated to any item in the block. Move the same trigger farther up to a form level, and it would fire when the user navigated to any item in any of the form’s blocks.

Triggers have relatively few properties. This makes sense for an object whose very name defines when it operates, and whose code content defines what it does. Table 15-1 shows the trigger properties and what they do.

In this section, you will cover the following points about producing triggers:

• Writing trigger code
• Understanding the use of built-in subprograms
• Introduction to the WHEN-WINDOW-CLOSED trigger

This section covers the basics of writing trigger code. It starts with an overview of the components you can use in a trigger code block. It then introduces the built-in subprograms that are supplied with Form Builder. It wraps up with an exercise in which you use a built-in subprogram within a form trigger.

Triggers consist of PL/SQL code blocks. The structure of these blocks will be familiar if you have completed the first two units of this book. (If you have not yet worked through those units, be sure to do so; the information they contain is essential to your success as a Developer/2000 developer.) Trigger code can contain a declaration section, a code section, and an error-trapping section. In addition, there are many new system variables enabling you to ascertain and control facets of the client/server environment provided by Form Builder; these are listed in Table 15-2. There are also many, many object properties that you can read and set using PL/SQL code. For instance, you could change the background color of an item in response to an event by placing code like this in the event’s trigger:

DECLARE
ITEM_ID item;
BEGIN
ITEM_ID := find_item('NET_EARNINGS');
set_item_property(ITEM_ID, visual_attribute, 'GREEN_TEXT_GROUP');
END;

In addition, there are many built-in packages containing PL/SQL constructs you can reference in your own code. Examples include CLEAR_ITEM, which clears a text item’s current value, and GET_FILE_NAME, which causes an file-open dialog box to display and returns information about the file selected to the routine that called it. The next section covers built-ins in greater detail. As you work through this chapter and the chapters that follow, you will have many opportunities to expand your trigger-writing skills.

In addition to system variables and object properties, there are many built-in subprograms, also known as built-ins, containing PL/SQL constructs you can use in your own trigger and subprogram code. Form Builder comes with hundreds of built-ins, and they are always available for use within your code. Examples include navigational functions such as NEXT_ITEM, NEXT_RECORD, or NEXT_BLOCK, as well as programming conveniences like GET_FILE_NAME, which generates a file-open dialog box and returns information about the selected file to its calling routine. The built-ins provided with Form Builder are grouped by function into packages. You can view the built-in packages in the Object Navigator by opening the Built-In Packages node. Table 15-3 shows the packages and their functional areas.

You have already used built-ins while doing this book’s exercises. For instance, when you created a form to display images you included a button employing the GET_FILE_NAME built-in to let the user specify a graphics file to be loaded. For reminder’s sake, the code used is shown here:

declare
v_dirname varchar2(255);
v_filename varchar2(255);
begin
v_dirname := 'C:\';

v_filename := get_file_name(v_dirname,NULL,
'Bitmap files (*.bmp)|*.bmp|' ||
'JPEG files (*.jpg)|*.jpg|' );

if v_filename is not null then
read_image_file(v_filename,'ANY','av_data_image.blob_data');
end if;
end;

This code also used the READ_IMAGE_FILE built-in to load the selected image file into the application’s current record memory. Both of these built-ins are part of the Standard Extensions package. When you use a built-in from a package other than the standard packages, you must precede its name with the name of the package; for instance, WEB.SHOW_DOCUMENT.

Because some of the built-ins provided in the Standard Extensions package are designed to cause navigation, a potential problem exists: what if a developer writes a trigger in response to a navigation event, and the trigger contains a built-in generating another navigation event causing the original trigger to fire again? This sort of circular-reference mistake would cause the application to hang, and it can happen with database transactions as well as navigation events. To keep this from happening, Form Builder prohibits navigation triggers from containing built-ins that move input focus or involve database transactions. These restricted built-ins cannot be called from PRE- and POST- triggers, because these triggers fire while the user navigates from one item to another. Examples of restricted built-ins include CLEAR_FORM, COMMIT_FORM, DELETE_RECORD, DOWN, ENTER, GO_ITEM, and NEXT_ITEM, among others.

The WHEN-WINDOW-CLOSED trigger fires whenever the user employs the window-manager Close command to close a window. To create one, open your SOUND_MODULE application. In the Object Navigator, open the Windows node and change the name of the application’s only window to SOUND_WINDOW. Then, go back up to the top of the Object Navigator and select the Triggers node directly beneath the module name. Click on the Create button, select the WHEN-WINDOW-CLOSED trigger, and click on the OK button. Enter the following code in the PL/SQL Editor for the trigger:

message('Executing the WHEN-WINDOW-CLOSED trigger. Preparing to close form…', ACKNOWLEDGE);
exit_form;

Compile the code, close the PL/SQL Editor, and run your application. When the application opens in the Forms Runtime program, click on the Windows Close button in the top-right corner of your form window. (The trigger does not fire if you use the Close button in the outer MDI window, or the Action | Exit menu command.) You should see a dialog box appear that looks similar to the one shown in Figure 15-1. Click on the dialog box’s OK button to return to Form Builder.

A key fact to remember about the WHEN-WINDOW-CLOSED trigger is that it is not attached to a window. Why? Look in the Object Navigator within the Windows node for your module. See any trigger subnodes? No—windows do not have triggers. So you have to go up a level, attaching the WHEN-WINDOW-CLOSED trigger at the form level, where it will fire when any window in the module is closed. Because of this, it is common to have the WHEN-WINDOW-CLOSED trigger contain an if statement that selects the trigger’s actions based on which window’s closing fired the trigger. An example of this, from the d2khelp.fmb demo file provided with Developer/2000, follows:

if :system.event_window = 'WIN_REL_TOPICS'
then
trt_hlp.hide_topics;
elseif :system.event_window = 'WIN_EXAMPLE'
then
trt_hlp.hide_example;
else
do_key('exit_form');
end if;

In this section, you will cover the following points about adding functionality to items on your forms:

• Supplementing the functionality of input items
• Supplementing the functionality of noninput items

The study of the triggers and built-in subprograms Form Builder offers for its input and noninput items is likely to be an ongoing process in your life as a developer. Here is an overview that will help you understand the forest as you learn about the trees.

All input items can have triggers attached to them. Because of the nature of their function—accepting data—input items commonly have triggers that fire when the user arrives on the item, leaves the item, or changes the item’s data. Any input item can have a WHEN-NEW-ITEM-INSTANCE trigger that executes each time the user lands on the item, as well as a WHEN-VALIDATE-ITEM trigger containing code to determine whether the content entered by the user satisfies your integrity constraints. Input items that function by having the user change their state rather than enter a value—meaning all input items except text items—can have triggers that fire when their contents change; the format used in those triggers’ names is WHEN-itemtype-CHANGED. Table 15-4 shows the common triggers for each type of input item.

Input item triggers can include built-ins to enhance their functionality. There are built-ins to manipulate data, such as CLEAR_ITEM, CLEAR_RECORD, ADD_LIST_ELEMENT, DELETE_LIST_ELEMENT, INSERT_RECORD, DELETE_RECORD, ENTER_QUERY, and EXECUTE_QUERY. There are also built-ins to move the input focus, such as GO_ITEM, GO_RECORD, GO_BLOCK; NEXT_ITEM, NEXT_RECORD, NEXT_BLOCK; and PREVIOUS_ITEM, PREVIOUS_RECORD, and PREVIOUS_BLOCK. In addition, there are built-ins to open specific items, such as SHOW_EDITOR and SHOW_LOV, as well as built-ins to control an item’s availability, like DISABLE_ITEM and ENABLE_ITEM. These built-ins operate like a PL/SQL function, expecting specific input parameters when they are called.

Like input items, noninput items can be augmented with triggers. Table 15-5 shows the common triggers for each type of noninput item.

Noninput item triggers can also employ built-ins to enhance their functionality. A noninput item trigger can employ any built-in used for an input item, as well as many other built-ins that control interface elements. Examples include CALL_FORM and CLOSE_FORM, ENTER_QUERY and EXECUTE_QUERY, OPEN_FORM and EXIT_FORM, and GET_TAB_PAGE_PROPERTY and SET_TAB_PAGE_PROPERTY. You may also find usefulness in GET_BLOCK_PROPERTY, GET_CANVAS_PROPERTY, and GET_FORM_PROPERTY; SET_BLOCK_PROPERTY, SET_CANVAS_PROPERTY, and SET_FORM_PROPERTY; SHOW_PAGE, SHOW_VIEW, SHOW_WINDOW; HIDE_PAGE, HIDE_VIEW, and HIDE_WINDOW; and SHOW_MENU, HIDE_MENU, and EXIT_MENU.

In this section, you will cover the following points about query triggers:

• Data block query process and triggers
• Writing triggers that screen query conditions
• Writing triggers to supplement query results

Believe it or not, a single query can fire over half a dozen triggers. There is a reason that Oracle Corporation expended the effort to offer this many points at which you can insert trigger code: control. Query triggers allow you to exert an enormous amount of control over the queries your application generates, both before they are created and after their data results have been returned. In this section you will learn how to utilize query triggers.

A query seems like a simple process: you identify what must be true about the records you want to see, and Oracle fetches them for you. When this is done via a Developer/2000 application, additional layers of actions are added relating to the presence of a user interface. The first group of actions falls under the category entering the query. When the user initiates a query in the Forms Runtime program, the program starts by checking whether the data block the user is in has its Query Allowed property set to Yes. If so, it checks to see if the block contains any items whose Query Allowed property is also Yes. If so, the Forms Runtime program allows the user to utilize Enter-Query mode to enter query conditions. The Forms Runtime program then takes the query conditions entered by the user in Enter-Query mode and adds whatever conditions were previously placed in the data block’s where clause property (this property can be changed by a trigger at run time, as you will soon see). The Forms Runtime program also appends any ordering specifications you have placed in the data block’s order by clause property (which can also be changed programmatically at run time).

The next stage is executing the query. Here, the Forms Runtime program once again checks whether the data block’s Query Allowed property is set to Yes (it does this again because this point can be reached by other means). Once that succeeds, it navigates to the relevant data block, validates any records that aren’t validated, and prompts the user to save any unsaved changes. Finally, it opens the query. If there are resulting records, it fetches the records, limiting their quantity to the number specified in the block’s Query Array Size property if the property is set to a value other than zero.

In addition to performing the actions just described, Developer/2000 has the ability to fire a variety of triggers during the process of fulfilling a query. For instance, there are pre-query and post-query triggers that you can attach to blocks or forms. These usually are attached to a block, where they can exert such control as specifying additional where column conditions (via a pre-query trigger) and setting values in fetched records before showing the records to the user (via a post-query trigger). To give you a more complete picture of when query triggers fire, Table 15-6 shows the order in which query triggers and actions execute. Whenever a trigger is on the same line as an action, the Forms Runtime program checks for the presence of the trigger first, and if the trigger exists, it is fired and the corresponding action does not occur.

Production: Please format left-hand column as subheadings.

The PRE-QUERY trigger gives you the ability to check a query entered by the user before that query is executed, and modify or stop the query if you wish. For instance, you can use a PRE-QUERY trigger to ensure that the user’s query includes at least one column that has an index, in order to maximize query turnaround speed and minimize server workload. You can also use a PRE-QUERY trigger to control how returned records are sorted, as well as to apply additional where clause criteria before the query is processed.

To practice this, you will modify your DEPARTMENT canvas so it lets users choose whether to sort departments by DEPARTMENT_ID or DEPARTMENT_NAME. Open your original sample application, click on the Data Blocks node, and click on the Create button. Select Build a new data block manually and click on the OK button. Change the new block’s Name property to DEPARTMENT_SORT_CONTROL and change its Database Data Block property to No to make it a control block. In the Object Navigator, click on the block’s Items node, followed by the Create button. Change the item’s name to SORT_SELECT, its Item Type property to Radio Group, its Initial Value to DEPARTMENT_ID, and its Canvas property to DEPARTMENT. Back in the Object Navigator, click on the SORT_SELECT item’s Triggers node, click on the Create button, select the WHEN-RADIO-CHANGED trigger, and enter the following code in the PL/SQL Editor:

go_block('DEPARTMENT');
execute_query;

Compile the trigger code and close the PL/SQL Editor. Now click on the SORT_SELECT item’s Radio Buttons node, followed by two clicks on the Create button. Change the first radio button’s Name and Label properties to ID, and its Radio Button Value property to DEPARTMENT_ID. Change the second radio button’s Name and Label properties to NAME, and its Radio Button Value property to DEPARTMENT_NAME. Next, in the Object Navigator, right-click on the DEPARTMENT_SORT_CONTROL block’s name and select Layout Wizard from the context menu that appears. Specify that you want to lay the control block out on the DEPARTMENT canvas, specify a form type of Form, enter a frame title of Department Sort Order, and then click on the Finish button. In the Layout Editor, move the control block so it is next to the DEPARTMENT block. Close the Layout Editor and return to the Object Navigator. Open the DEPARTMENT data block’s node, click on the Triggers node, and click on the Create button. Select the Pre-Query trigger and enter the following code in the PL/SQL Editor:

set_block_property('DEPARTMENT', ORDER_BY, :department_sort_control.sort_select);

Compile the trigger code and close the PL/SQL Editor. Save your application and then run it. In the Forms Runtime program, navigate to the Department window, and then click on the NAME radio button. That should initiate a query whose results look very similar to those in Figure 15-2. When you are through, close the Forms Runtime program and return to Form Builder.

If you wanted to create a pre-query trigger to ensure that a user’s query includes criteria on the indexed column DEPARTMENT_ID, you could do so with code similar to this:

if :DEPARTMENT.DEPARTMENT_ID is null then
message('A Department ID is required. Please provide '||
'a Department ID and re-run your query.');
raise form_trigger_failure;
end if;

You can also specify additional where clause filtering by using code like the following (which assumes, for the sake of demonstration, that a check box exists identifying whether the user wants their employee form to show only employees in the profit-sharing plan):

if checkbox_checked('EMPLOYEE_FILTER_CONTROL.PROFIT_SHARE_ONLY') then
set_block_property('EMPLOYEE', DEFAULT_WHERE, 'PROFIT_SHARING_INDICATOR = 1');
end if;

You can use all of these techniques together in a single Pre-Query trigger, giving you the assurance of index utilization while giving your users the ability to easily tailor the data returned without having to go through Enter-Query mode.

Because the Post-Query trigger fires after records are retrieved but before they displayed to the user, you can use it to augment a query’s records in a number of ways. The Post-Query trigger can contain code to calculate running totals, generate statistics about the records retrieved, or populate control items, as well as items in other blocks.

The following example builds on the techniques you practiced in the section you just completed. The example code, designed to be run from an Employee form, uses the employee’s DEPARTMENT_ID to retrieve that department’s mission statement from the DEPARTMENT table and display it in on the EMPLOYEE canvas. The example code assumes that the DEPARTMENT table has a column named MISSION to store mission statements, and that the Employee canvas has a display item named DEPT_MISSION_DISPLAY to show a mission statement.

DECLARE
cursor MEM_DEPT_MISSION is
select MISSION
from DEPARTMENT
where DEPARTMENT_ID=:EMPLOYEE.DEPARTMENT_ID;
BEGIN
open MEM_DEPT_MISSION;
fetch MEM_DEPT_MISSION into :EMPLOYEE.DEPT_MISSION_DISPLAY;
close MEM_DEPT_MISSION;
END;

In this section, you will cover the following points about debugging triggers in your forms:

• Running a form module in debug mode
• Understanding the components of the Debugger
• Debugging PL/SQL code

Nothing is more fun than debugging trigger code. Okay, that may not be true. But using decent debugging tools can make the process a lot more pleasant. This section covers the fundamentals of using the Forms Runtime program’s Debugger. You will start with an overview of the debugging process used for Developer/2000 forms. Then, you will learn specifics about debugging options and initiating the debug process. Next, you will get a thorough explanation of the Debugger’s screen components, followed by a walk-through on setting debug breakpoints, altering memory values while the application is paused, and seeing the impact of altered values when the application resumes.

When you run your form module in debug mode, the Forms Runtime program starts, but instead of going immediately into your application, it first displays the Debugger. While in the Debugger you can create breakpoints and even write coded debug triggers that fire at specific times. After you have established breakpoints and debug triggers—or if you don’t yet know where you will need them—you close the Debugger, and your application starts. From that point on, the evidence that you are in debug mode varies depending on how you configured your debug settings before running your form. You may see evidence that you are running in debug mode every time a trigger fires, or whenever the program’s execution is interrupted with a break command in your code, or when you choose Help | Debug from the Forms Runtime menu.

You can start the Debugger from Form Builder by clicking on the Debug Mode button, shown here:

When this button is active, the Debugger will start each time you run your form from Form Builder. Behind the scenes, enabling this button causes Form Builder to include source code in the .fmx and .mmx files it generates before calling the Forms Runtime program. Since part of the Debugger’s job is letting you view and modify source code at run time, it is necessary to have that source code in the executable file.

There is one other debug option you set from Form Builder: Debug Messages. To set this option, execute the Tools \ Preferences \ Runtime menu command. The resulting dialog box contains a check box labeled Debug Messages. When you run your form in debug mode with this option enabled, the Forms Runtime program displays an alert every time a trigger is about to fire. In addition, it displays in the form console the name of the trigger, along with the item that the trigger is attached to. This option can be valuable, because it lets you pinpoint precisely the spot in an application where a problem is occurring. On the other hand, simple actions can generate a lot of trigger events, and if you already have a good idea where a problem is occurring, your debugging may be slowed down substantially by enabling the Debug Messages option. For the sake of learning, enable it now and close the dialog box.

Before running your form, ensure that the Debug Mode button is enabled. Then click on the Run Form button to start the Forms Runtime program. Before your application starts, you will see the Debugger screen, which is discussed in detail in the next section.

In order to use the Debugger effectively you must be thoroughly acquainted with its components. Figure 15-3 shows the Debugger window and identifies its main components.

The Debugger toolbar provides quick access to functions commonly used while debugging. The toolbar is shown in detail in Figure 15-4. The Step Into button instructs the Debugger to step into subprogram calls. In contrast, the Step Over button disables stepping into called subprogram bodies. The Step Out button resumes execution, and stays in effect until the current subprogram has returned. The Go button also resumes program execution, remaining in effect until the thread currently executing terminates or is interrupted by a debug action. The Reset button stops program execution in the current debug level and returns program control to an outer level. The Close button closes the Debugger window so you can proceed to run your form. The Expand, Collapse, Expand All, and Collapse All buttons cause Object Navigator nodes to open and close, either individually or as a group.

Below the Source Pane is the Debugger’s Object Navigator. This area lists all the modules that the Debugger is going to watch. You can see your module by clicking on the + to the left of the Modules node. When you do, you will see your sample application module. Open it, open its Blocks node, open the DEPARTMENT node, open the Triggers subnode, and click on the PRE-QUERY trigger. You will see the trigger’s code appear in the Source Pane above.

Underneath the Object Navigator is the Interpreter. This is an interactive prompt at which you can enter commands that control the actions of the Debugger, such as creating breakpoints. Much of what you can do at the Interpreter prompt can also be done by selecting items in the Source Pane or the Debugger Object Navigator, or by executing commands from the new menu command groups that appear in your Forms Runtime menu when the Debugger is active: View, Navigator, Program, and Debug.

When you run a form module in debug mode and the Debugger presents its screen, it is likely you will want to simply dismiss the Debugger and run your application until you observe the problem you are trying to solve. Dismiss the Debugger now by clicking on the Close button in its toolbar. Then navigate to your Department form. If you enabled the Debug Messages option in Form Builder, you will immediately be presented with an alert box stating simply "Please acknowledge message." Down in the console, the message line will identify that a trigger is about to fire; it will name which trigger, and specify what object owns the trigger. Click on the alert box’s OK button to proceed, and then populate the Department form by clicking on the NAME radio button in the Department Sort Order control block. This will cause several alert messages to display—one for each trigger that fires while the program fulfills your request. As you can see, the Debug Messages option gives you a very fine razor…and can get annoying after a while. To turn it off, exit from the Forms Runtime program, execute the Form Builder’s Tools | Preferences | Runtime command, and uncheck the Debug Messages option. Then click on the OK button and rerun your form. This time, don’t dismiss the Debugger right away. Within its Object Navigator, navigate to the PRE-QUERY trigger you created earlier for the DEPARTMENT table. When you find it, the trigger’s code will be displayed in the Debugger’s Source Pane. Double-click on the set_block_property line in the trigger code. You will see that its line number changes to ‘B(01)’. You have just set a breakpoint, which will cause the Debugger to be invoked just before your application executes that line of trigger code. You can remove the breakpoint by double-clicking on the line again. You also could have established a breakpoint here when you created the trigger in Form Builder by including a line containing the following code:

break;

The break command can be especially handy as part of an if statement that checks for valid conditions and executes the break command if it finds a problem.

Ensure now that the set_block_property line in your trigger is marked as a breakpoint, and then click on the Close button to proceed to run your application. Navigate to the Department form and click on the NAME radio button. When the Forms Runtime program encounters the breakpoint in your PRE-QUERY trigger, it will stop and open the Debugger. At this point your screen should look similar to Figure 15-5. If you were tracking down a real problem, you would now use the Debugger’s Object Navigator to investigate the values stored in your module, global variables, or system variables. For example, open the Object Navigator’s Modules node and navigate down to your DEPARTMENT_SORT_CONTROL block. Open its Items node and you will see that the only item it has—SORT_SELECT—has a current value of DEPARTMENT_NAME. It has that value because the most recent action was initiated by the NAME radio button, and you assigned that radio button a value of DEPARTMENT_NAME when you created it. To see how you can use the Debugger to alter values during execution, click on the DEPARTMENT_NAME value to the right of the SORT_SELECT item, and replace the value with DEPARTMENT_ID. Press the enter key to make the new value "stick," and then click on the Go button to continue. You will see that the department data has been sorted by ID, and the radio group has its ID button marked, even though it was the NAME button that initiated the action. The results were different because you altered the value midstream using the breakpoint and the Debugger.

In this chapter, you have covered a substantial amount of information on working with triggers. You started with an explanation of what triggers are and how they work. You then learned how to produce triggers, and you employed this knowledge while writing triggers that add functionality to items and queries. Next, you learned how to debug triggers using the Debugger.

The first area you covered was an introduction to form triggers. A 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 the user clicks on the button to which the trigger is attached. The triggers most commonly used in Form Builder fall into several functional categories: Block Processing, Interface Event, Master/Detail, Message Handling, Navigational, Query, Transactional, and Validation. Most of the triggers you deal with will be interface event triggers, which fire as the user interacts with your application’s GUI objects. Interface event triggers generally have names fitting the format of WHEN-object-action. Triggers that fire when a new instance of an object is created follow a naming format of WHEN-NEW-object-INSTANCE. Mouse event triggers use the naming format WHEN-MOUSE-mouse action, and triggers that respond to keystrokes use the format KEY-keytype. The KEY-OTHERS trigger fires whenever the user presses a key that can have a trigger but does not, and it is an excellent way to disable unwanted function keys. One trigger whose name does not represent the object it is attached to is WHEN-WINDOW-CLOSED. Because windows do not have triggers, the WHEN-WINDOW-CLOSED trigger is usually attached at the form level.

Triggers can also be grouped by their type and scope. The two types of triggers are "built-in" and "user-named." Built-in triggers are supplied with Form Builder, and they each correspond to a predefined runtime event. User-named triggers, on the other hand, are not provided with Form Builder; they are written by developers like you, and their names can be whatever the developer desires. A trigger’s scope defines what event must occur in order for the trigger to fire, and is determined by the object to which the trigger is attached. Triggers have few properties, the most important of which are Execution Hierarchy, which determines how the Forms Runtime program handles triggers with identical names and overlapping scopes; and Display In Keyboard Help, which gives you the ability to add a description of a KEY- trigger to the key help in the Forms Runtime program.

After establishing the basic premises of trigger functions, categories, types, scopes, and properties, your attention turned to the techniques involved in producing triggers. Triggers consist of code blocks filled with PL/SQL instructions. Trigger code can also contain a variety of system variables, commands to read and set object properties, and built-in packages containing premade PL/SQL constructs for specific purposes. The built-ins provided with Form Builder are grouped by function into packages. When you use a built-in from a package other than the Standard packages, you must precede its name with the name of the package; for instance, WEB.SHOW_DOCUMENT. Built-ins that move input focus or involve database transactions cannot be used in navigation triggers, so as to avoid circular references that generate endless command loops. These built-ins are called restricted built-ins.

When addressing how to use triggers to add functionality to form items, you learned that all input items can have triggers attached to them. Input items commonly have triggers that fire when the user arrives on the item, leaves the item, or changes the item’s data. Any input item can have a WHEN-NEW-ITEM-INSTANCE trigger that executes each time the user lands on the item, as well as a WHEN-VALIDATE-ITEM trigger to check the item’s contents for integrity. Input items that function by having the user change their state rather than enter a value—meaning all input items except text items—can have triggers that fire when their contents change; the format used in those triggers’ names is WHEN-itemtype-CHANGED. Input item triggers can make use of built-ins that manipulate data, move the input focus, open editors and LOVs, and control an item’s availability. Like input items, noninput items can be augmented with triggers, generally including a WHEN-NEW-ITEM-INSTANCE trigger and a trigger that fires when the item is clicked with the mouse. Noninput item triggers can also employ built-ins to enhance their functionality. A noninput item trigger can make use of any built-in used for an input item, as well as many other built-ins that control interface elements.

You then proceeded to learn about query triggers. You started by learning the process involved in executing a data-block query, which consists of three steps: entering the query, executing the query, and fetching the records. A data query can have several triggers attached to it, the most useful of which are the PRE-QUERY trigger (which fires before the select statement is finalized) and the POST-QUERY trigger (which fires before selected records are presented to the user).

The final area covered in this chapter is debugging. The Debugger can be invoked by enabling the Debug Mode option in Form Builder and then running your form. When the Debug Mode button is active, the Debugger will start each time you run your form from Form Builder. Enabling this button causes Form Builder to include source code in the .fmx and .mmx files it generates before calling the Forms Runtime program. You can also choose to enable Debug Messages, which cause the Forms Runtime program to display an alert every time a trigger is about to fire, along with a message in the console identifying the trigger and object that owns it. The Form Debugger has four visual components: the toolbar, which contains buttons for often-used actions; the Source Pane, which shows the PL/SQL instructions in whatever code object you select; the Object Navigator, which displays Debugger objects in a hierarchical layout; and the Interpreter, which is an interactive prompt at which you can type Debugger commands. While in the Debugger, you can create temporary breakpoints and write coded debug triggers that fire in response to specific actions. You can also create breakpoints in Form Builder by adding the command break to your PL/SQL code. Whenever the Forms Runtime program encounters either type of breakpoint, it pauses execution of your application and starts the Debugger, which allows you to examine and change memory variables and program code.

All in all, this chapter comprises about 22 percent of material tested on OCP Exam 3.