Figure 14.5.
Using the PL/SQL
Editor to specify the
POST-QUERY trigger.

Listing 14.2. Specifying a POST-QUERY trigger.

select title
into :class.course_title
from course
where
course_id = :class.course_id and
department_id = :department.department_id;

Let's try running the form to see if the trigger works correctly.

1. Click on the green signal light on the vertical toolbar.
2. To execute a query against the Department block, click on the Query button.
3. In Department_ID, enter HIST.
4. Click on the Query button once more. As you can see in Figure 14.6, the trigger performed as expected—the title for each course was retrieved from the Course table.

Now, let's see what happens when a new class is entered.

1. Click the pointer in Class_ID. As illustrated in Figure 14.7, the title is not retrieved for Course_ID 199. As you can see, an item-level trigger is needed for Course_ID.
2. Exit the Forms Runtime.

Figure 14.6.
Testing the trigger.

Figure 14.7.
Post-query doesn't
work when a new
record is inserted.

3. Using the Object Navigator, expand the Course_ID item.
4. In the Triggers node that belongs to Course_ID, click + in the vertical toolbar to add a trigger.

5. In the window that appears, select the WHEN-VALIDATE-ITEM trigger. Although the same SELECT statement that you used for the block-level trigger—POST-QUERY—is used for this item-level trigger, the statement is enclosed in an anonymous block, as shown in Figure 14.8.

Figure 14.8.
Specifying the WHEN-
VALIDATE-ITEM
trigger for Course_ID.

6. In addition, you should add an exception section to the trigger to handle the situation in which the user enters an invalid Course_ID. However, because you specified that the Course_ID_LOV be used to validate the Course_ID field, the exception should never be raised.

Listing 14.3. WHEN-VALIDATE-ITEM trigger for Course_ID field.

begin
    select title
    into :class.course_title
    from course
    where
    course_id = :class.course_id and
    department_id = :department.department_id;
exception
    when no_data_found then
      message (`Invalid Course ID. Please enter a valid Course ID.');
      raise form_trigger_failure;
end;

Let's run the form again to test the item-level trigger.

1. Execute a query against the Department block by clicking Query.
2. In Department_ID, enter HIST.
3. Click Query once more.
4. Click the pointer in Class_ID.
5. Enter 109999 in Class_ID.
6. Tab to Course_ID, and enter 199.
7. Press Tab once more. Figure 14.9 demonstrates that the trigger is working correctly; the correct title for History 199—"WORKSHOP ON JEFFERSON"—is displayed in Course_Title.

Figure 14.9.
Testing the WHEN-
VALIDATE-ITEM
trigger for Course_ID.

Using a Form-Level Trigger to Execute
a Query

Let's work through another sample trigger. Follow these steps to create a form that, when invoked, will automatically execute a query:

1. Using the Forms Designer, create a new form by selecting File | New | Form from the menu.
2. Name the new form INSTRUCTOR, and create a new block based on the Instructor table. When you are finished designing your form, the layout should look similar to what is shown in Figure 14.10.

Figure 14.10.
Creating an Instructor
form.

3. If you use the Object Navigator to view the Instructor form, you'll see that there aren't any triggers at the form level, which is directly beneath the form itself. To create a form-level trigger, select the Triggers node and click + on the vertical toolbar.
4. In the window that appears, select WHEN-WINDOW-ACTIVATED and click OK. This trigger will fire when the form is invoked.
5. The PL/SQL Editor will appear. You need to write only a single line of PL/SQL code for this trigger: a call to execute_query; which is a built-in Oracle Forms procedure. This built-in procedure will execute a query in the current block, which is Instructor.
6. Click Close, and test the form by clicking the green signal light on the vertical toolbar. As you can see in Figure 14.11, the form will automatically execute a query when it is invoked.

Figure 14.11.
Testing the WHEN-
WINDOW-ACTIVATED
trigger.

Using a Trigger to Validate User Input

In this section, you investigate another use for an item-level trigger: to validate user input. Suppose that the college president has told you that if an instructor is added to a department that already has at least one instructor who is a full professor, the new instructor cannot also be a full professor—he or she must be either an assistant or associate professor.

To do this, you will modify the existing WHEN-VALIDATE-ITEM trigger for the Position text item by expanding the Position text item and double-clicking the icon to the left of the WHEN-VALIDATE-ITEM trigger. As you can see in Figure 14.12, the existing trigger performs the same logic as the CHECK constraint on the Position column in the Instructor table. This trigger was automatically generated when you selected the Integrity Constraints when the block was created.

Listing 14.4 shows the WHEN-WINDOW-ACTIVATED trigger after it has been modified to perform this logic.

Listing 14.4. Modified WHEN-WINDOW-ACTIVATED trigger.

declare
num_full_professors  integer;

begin
if not( :INSTRUCTOR.POSITION IN (`ASSISTANT PROFESSOR',
Â'ASSOCIATE PROFESSOR', `PROFESSOR') ) then
  message( `WHEN-VALIDATE-ITEM trigger failed on field - `
Â|| :system.trigger_field );

  raise form_trigger_failure;
end if;

select count(*) into num_full_professors
from Instructor
where Department_ID = :Instructor.Department_ID and
ÂPosition = `PROFESSOR';

if num_full_professors > 0 and :Instructor.Position = `PROFESSOR' then
  message (`Cannot add another full professor to this department.');
  raise form_trigger_failure;
end if;


end;

ANALYSIS
First, the PL/SQL statements are contained in an anonymous block. A variable named num_full_professors is declared. The first section of the block is used to enforce the CHECK constraint. Next, a SELECT statement is used to determine how many full professors exist in the department. If that number is greater than zero, a message is displayed, and a built-in exception, FORM_TRIGGER_FAILURE, is raised which prevents the user from continuing until the value is corrected.

Let's test the trigger. To create a new instructor, select Record | Insert from the menu. Try entering a new instructor for the anthropology department which already has two full professors, Boris Hitchcock and Ranier Poulson. As you can see in Figure 14.13, the trigger is working. As intended, the trigger will not prevent you from adding a full professor to the math department because that department does not already have a full professor.

Figure 14.12.
Existing code in the
WHEN-WINDOW-
ACTIVATED trigger.