Listing 16.2. PL/SQL block for the database trigger body.

begin

if inserting then

  insert into student_schedule_journal
  (operation, student_ID, Class_ID, Grade, Date_Grade_Assigned,
   Changed_By, Changed_Date)
  values
  (`I', :new.Student_ID, :new.Class_ID, :new.Grade,
   :new.Date_Grade_Assigned, user, sysdate);

elsif updating then

  insert into student_schedule_journal
  (operation, student_ID, Class_ID, Grade, Date_Grade_Assigned,
   Changed_By, Changed_Date)
  values
  (`U', :new.Student_ID, :new.Class_ID, :new.Grade,
   :new.Date_Grade_Assigned, user, sysdate);

elsif deleting then

  insert into student_schedule_journal
  (operation, student_ID, Class_ID, Grade, Date_Grade_Assigned,
   Changed_By, Changed_Date)
  values
  (`I', :new.Student_ID, :new.Class_ID, :new.Grade,
   :new.Date_Grade_Assigned, user, sysdate);

end if;

end;

ANALYSIS
As you can see in Listing 16.2, the trigger body consists of a PL/SQL block with an IF statement. Each clause in the IF statement tests for the operation that is being performed. For instance, the first portion of the IF statement tests to see if the triggering event is an INSERT; if so, a row is inserted into the Student_Schedule_Journal with the following values. Click Close to exit the Database Trigger Editor.

Testing the New Database Trigger

Let's test the trigger. You can do this by using either SQL*Plus or SQL Worksheet.

1. Connect to the flugle account.
2. To begin with, verify that the Student_Schedule_Journal table is empty (see Figure 16.20).

Figure 16.20.
Student_Schedule_Journal
table is empty.

3. Retrieve all records from the Student_Schedule table in which the Student_ID is 10231324 (who happens to be Anna Anastatia). As you can see in Figure 16.21, there is only one class listed: 109100 (which happens to be Biology 101).
4. Anna worked hard this semester—she deserves an A in the class. To assign her an A in the course, use an UPDATE statement to set the value of GRADE to an A (see Listing 16.3).

Figure 16.21.
Viewingrecords in the
Student_Schedule
table.

Listing 16.3. Assigning a grade with an UPDATE statement.

update Student_Schedule
set
Grade = `A',
Date_Grade_Assigned = sysdate
where
Student_ID = `10231324' and

Class_ID = `109100';

As shown in Figure 16.22, the Student_Schedule_Journal now has a single row with the expected values, including:

Figure 16.22.
Verifying that the
trigger has fired.

Summary

This lesson explored these key concepts regarding Oracle Graphics and Procedure Builder:

• Oracle Graphics consists of two programs: Graphics Designer and Graphics Runtime.
• You design a chart by defining a query and a chart type. A query can be based on a single table, a join of two or more tables, or a view.
• Oracle Graphics provides many options for customizing the appearance and layout of a chart.
• Procedure Builder is a convenient tool for modifying PL/SQL subprograms in a variety of sources, including stored program units, libraries, and database triggers.
• The Stored Program Unit Editor enables you to create, view, modify, and drop stored procedures, stored functions, package specifications, and package bodies.
• If you are connected to an Oracle account with the proper privileges, you can view the stored program units and database triggers owned by other Oracle accounts.
• You can create, modify, and drop a database trigger with the Database Trigger Editor. You can specify the operations that will cause a database trigger to fire, as well as when the trigger will fire (before or after the operation).

What Comes Next?

On Day 17, "Introduction to Oracle Power Objects," you will learn about this intuitive, object-oriented tool for developing client/server applications. Power Objects differs significantly from Oracle Forms. It uses a scripting language named Oracle Basic.

Q&A

Q Does Oracle Graphics offer the capability to "drill-down" into a chart for the purpose of seeing the details of a chart element?

A Yes. You can specify a detail query that will be executed when the user clicks on an element in the chart. The main chart is referred to as the master chart. A parameter is created that is used to define the relationship between the master and detail charts.

Q What are some of the built-in packages that you can view with Procedure Builder?

A The built-in packages include

• TEXT_IO for reading from and writing to files
• DDE, which provides Dynamic Data Exchange support within Oracle Forms, Reports, Graphics, and other Windows-based programs
• TOOL_ENV for obtaining the values of Oracle environment variables

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercises before you continue with tomorrow's lesson.

Quiz

1. What are the two file types used by Oracle Graphics?
2. Name three chart types that can be specified with the Graphics Designer.
3. True or false? You can use the Stored Program Unit Editor to modify a package specification but not a package body.

Exercises

1. Create a bar chart that displays the number of courses offered by each department at Flugle College.
2. Modify the trigger on the Student_Schedule table so that it records changes to the Student_Schedule table only if the value of the Grade column is A, B, or C.