245-248

Previous

Table of Contents

Next

Step-By-Step: Creating A Trigger

Let s design a trigger from scratch now, based on a simple set of rules that the trigger must enforce. We ll create a trigger that prevents a students from enrolling in courses, unless the student satisfies a minimum student level (freshman, sophomore, junior, senior, master s, or doctoral).

Trigger Requirements

While the final functionality of the trigger is a rule of the university, the design of the trigger must be grounded in the trigger s associated table and any relationships between the associated table and other tables. For each student s classes, a row must exist in the ENROLLED_CLASSES table, which has this structure:

 ssn                    NOT NULL   varchar2 (9) course_number          NOT NULL   number   (5) audit_flag                        varchar2 (1) 

The ssn column in the ENROLLED_CLASSES table has a foreign key relationship to the ssn column in the STUDENTS table, as follows :

 ssn                    NOT NULL   varchar2 (9) first_name             NOT NULL   varchar2 (10) last_name              NOT NULL   varchar2 (12) street_address         NOT NULL   varchar2 (30) apartment_number       NOT NULL   varchar2 (4) city                   NOT NULL   varchar2 (30) state_code             NOT NULL   varchar2 (2) zip_code               NOT NULL   number   (5) home_phone             NOT NULL   number   (10) financing_num          NOT NULL   integer  (9) student_level          NOT NULL   number   (1) degree_plan_code                  number   (5) overall_gpa                       number   (3, 2) most_recent_gpa                   number   (3, 2) middle_name                       varchar2 (10) 

The course_number column in the ENROLLED_CLASSES table has a foreign key relationship with the SCHEDULED_COURSES table, as follows:

 course_number          NOT NULL   number   (5) course_credits         NOT NULL   number   (1) course_hours           NOT NULL   number   (1) course_time            NOT NULL   varchar2 (2) course_location        NOT NULL   number   (5) min_student_level                 number   (1) credit_flag                       varchar2 (1) 

This means that our trigger must retrieve information from two different places (the SCHEDULED_COURSES and the STUDENTS tables) to determine whether the student can enroll in the class.

Now that we ve examined the data structures that we ll have to deal with to implement the trigger, let s move on and start examining how the trigger has to work.

Determining The Trigger s Level

One of the most important steps in designing a trigger is making sure the trigger fires at the right time. This is determined by the way the trigger s base table is used in day-to-day operations.

In order for a student to register for a class, a row must be created in the ENROLLED_CLASSES table. Because INSERT statements are used to create rows, the trigger we create must obviously fire when a new row is inserted into the table.

The real question is whether rows that already exist in the ENROLLED_CLASSES table can be updated. It turns out that a student could very well update a row by deciding to take a specific class at a different time or in a different location. This means that the trigger must also fire when an UPDATE statement is executed on the ENROLLED_CLASSES table.

Because each row in the ENROLLED_CLASSES table represents a single class, the trigger must fire for every row in the table.

Pseudocode

We now know that the trigger must accomplish the following tasks whenever a new row is created or an existing row is modified in the ENROLLED_CLASSES table:

   Determine a student s level.

   Determine the minimum level of student that can take a course.

   Compare the student s level to the minimum level for a course.

Based on this definition of what the trigger has to do, we can develop some pseudocode for the trigger that clearly illustrates the logical steps necessary to enforce the business rules. This pseudocode is shown in Listing 7.18.

Listing 7.18 Pseudocode for the ENROLLED_CLASSES_ARIU trigger.

 for each row created or updated loop    get the student's current level;    get the minimum level for the course;    if the student's level is less than the course level then       raise an error;    end if; end loop; 

Previous

Table of Contents

Next