Week 2

Day 14

Developer/2000:
Application
Development with
Oracle Forms

In the previous two lessons, you built two forms: one form based on a single table and a master-detail form. You also learned how to modify the default layout that is generated by the Forms Designer when a new block is created.

This is your final lesson on Oracle Forms. In it, you investigate the use of PL/SQL to construct application triggers that serve a variety of purposes. You also go through the steps that can be used to develop a multi-form application. Along with that, you learn how to construct a menu that will invoke different forms.

An Overview of Triggers

NEW TERM
A trigger is one or more PL/SQL statements that execute when an event occurs. The event may be directly related to an action performed by the user, such as pressing a button. Or the event may be indirectly related to an action performed by the user, such as the period before a query is executed.

A trigger can be defined at three levels:

• At the form level
• At the block level
• At the item level

Often, a single form will have one or more triggers at each level.

Default Triggers

In the previous lesson, you created a master-detail form named Department_Class. When you created each block, you checked a checkbox on the Layout tab to indicate that integrity constraints should be enforced. If you use the Object Navigator to expand the Department block, you will see three triggers that were created when you defined the Department block (see Figure 14.1):

• ON-CHECK-DELETE-MASTER
• ON-POPULATE-DETAILS
• KEY-DELREC
  

Figure 14.1.
Block-level triggers
created to enforce
integrity.

Page 381

How do these triggers enforce referential integrity? Let's look at the KEY-DELREC trigger in detail. This trigger is fired when the user presses the Delete key. Listing 14.1 contains the text of this trigger. Notice that the trigger is composed of two anonymous PL/SQL blocks. The first block determines if there are any classes associated with the department to be deleted, and, if there are, issues an error message and raises an exception. The second block is very similar to the first block; it determines if any instructors belong to the department to be deleted and, if so, takes the necessary actions. You can view a trigger by double-clicking it in the Object Navigator.

Listing 14.1. KEY-DELREC trigger code for the Department block.

declare
    cursor primary_cur is select `x' from FLUGLE.CLASS
        where DEPARTMENT_ID = :DEPARTMENT.DEPARTMENT_ID;
    primary_dummy  char(1);
begin
    if ( ( :DEPARTMENT.DEPARTMENT_ID is not null ) ) then
        open primary_cur;
        fetch primary_cur into primary_dummy;
        if ( primary_cur%found ) then
           message(`Cannot delete master record when matching detail
Ârecords exist.');
           close primary_cur;
           raise form_trigger_failure;
        end if;
        close primary_cur;
    end if;
end;

declare
    cursor primary_cur is select `x' from FLUGLE.COURSE
        where DEPARTMENT_ID = :DEPARTMENT.DEPARTMENT_ID;
    primary_dummy  char(1);
begin
    if ( ( :DEPARTMENT.DEPARTMENT_ID is not null ) ) then
        open primary_cur;
        fetch primary_cur into primary_dummy;
        if ( primary_cur%found ) then
           message(`Cannot delete master record when matching detail
Ârecords exist.');
           close primary_cur;
           raise form_trigger_failure;
        end if;
        close primary_cur;
    end if;
end;

declare
    cursor primary_cur is select `x' from FLUGLE.INSTRUCTOR
        where DEPARTMENT_ID = :DEPARTMENT.DEPARTMENT_ID;
    primary_dummy  char(1);



                                                     continues

Listing 14.1. continued

begin
    if ( ( :DEPARTMENT.DEPARTMENT_ID is not null ) ) then
        open primary_cur;
        fetch primary_cur into primary_dummy;
        if ( primary_cur%found ) then
           message(`Cannot delete master record when matching detail
Ârecords exist.');
           close primary_cur;
           raise form_trigger_failure;
        end if;
        close primary_cur;
    end if;
end;

delete_record;

Using a Trigger to Retrieve Values from Another Table

Let's work through an example of how a trigger can be used in the Department_Class form. If you look at the Class block, the Course_ID is displayed, but unless the user has mem-
orized the Course IDs, he or she has no way of knowing anything else about the course. The user probably would benefit if the title of the course were displayed.

1. Using the Layout Editor, move Schedule_ID, Building, Room, and Instructor_ID to the right so that the course title can be displayed to the right of Course_ID.
2. Next, select the Text Item tool on the vertical toolbar, and left-click to the right of Course_ID (see Figure 14.2).
   Don't worry about the size and spacing of the new text item; you'll change it in a moment.
3. Right-click Properties to change the properties of the new text item.
4. In the Name property, enter COURSE_TITLE. Scroll down to the group of properties labeled Navigation. Set the Navigable property to False by pressing Enter.
5. The Course_Title text item will be used only to display the course title; there is no reason the user needs to navigate to it. Scroll down to the group of properties labeled Database.
6. Set the Base Table Item property to False by pressing Enter (see Figure 14.3).
   The reason you are setting the Base Table Item property to False is because the Class block is based on the Class table, and the Course_Title is not a column in the Class table.

Figure 14.2.
Adding a text item to
the Class block.

Figure 14.3.
Setting the properties
for the Course_Title
text item.

7. Let's return to the Object Navigator. Drag the COURSE_TITLE text item beneath the COURSE_ID text item.
8. Select the COURSE_ID text item, and right-click Properties. Scroll down to the Height property.

9. In the Object Navigator window, select the COURSE_TITLE text item by clicking it.
   Notice that the Properties window now displays the properties for COURSE_TITLE and that the focus is still on the Height property.
10. Change the height to the same value used for COURSE_ID: 14.

Now that you have a place to display the course title, you still need a trigger that will cause it to be displayed.

1. Use the Object Navigator to expand the triggers that belong to the Department block, and click + on the vertical toolbar to create a new block-level trigger.
2. A window will appear from which you can select the event that will cause the new trigger to fire; select POST-QUERY and click OK (see Figure 14.4).
   

Figure 14.4.
Creating a POST-
QUERY trigger for
the Department block.

3. The PL/SQL Editor will appear. As shown in Figure 14.5, enter a SELECT statement that will retrieve Course_Title for the value for Course_ID found in the Course_ID item in the Class block and the value for Department_ID found in the Department_ID item in the Department block.

4. Enter the SELECT statement as shown in Listing 14.2, and click on Compile. If you've typed the statement correctly, you shouldn't see any error messages. Click on Close.