248-251

Previous

Table of Contents

Next

Code

Now that we ve written some simple procedural descriptions for the trigger, we re ready to take a crack at coding. Listing 7.19 is the code for the ENROLLED_CLASSES_ARIU trigger.

Listing 7.19 Code for the ENROLLED_CLASSES_ARIU trigger.

 TRIGGER ENROLLED_CLASSES_ARIU AFTER INSERT OR UPDATE ON ENROLLED_CLASSES FOR EACH ROW DECLARE    iStudentLevel            integer;    iCourseLevel             integer;       xSTUDENT_NOT_QUALIFIED   EXCEPTION; BEGIN    SELECT student_level    INTO   iStudentLevel    FROM   STUDENTS    WHERE  ssn = :new.ssn;    SELECT nvl (min_student_level, 0)    INTO   iCourseLevel    FROM   SCHEDULED_COURSES    WHERE  course_number = :new.course_number;    IF (iStudentLevel < iCourseLevel) THEN       RAISE xSTUDENT_NOT_QUALIFIED;    END IF; END; 

You might have noticed that the final code for the trigger uses the nvl() func- tion when retrieving the value of the min_student_level column from the SCHEDULED_COURSES table. This allows us to have a course that can be taken by any student.

Testing The Trigger

To ensure that mutating table errors are avoided, every test of a database trigger should incorporate the following tasks :

   Create a single new row in the trigger s associated table.

   Create multiple new rows in the trigger s associated table.

   Modify a single row in the trigger s associated table.

   Modify multiple rows in the trigger s associated table.

   Delete a single row in the trigger s associated table.

   Delete multiple rows in the trigger s associated table.

Obviously, not all of these tests will be valid for a single trigger, but one of the principles of testing is to test events that shouldn t cause a response. For instance, if you ve created a row-level INSERT trigger, executing an UPDATE statement against the trigger s associated table shouldn t cause the trigger to fire.

In addition to the previously outlined tests, a test for a database trigger must also account for different sets of conditions that exist in the data. Based on what we know about the trigger that we just wrote, tests such as the following should be run to test the INSERT functionality of the trigger:

   Create a new student, John Williams, with a student_level of 1 (freshman) and a new course, Psychology 101, with a min_student_level of 1. Attempt to register the student for the course by inserting a row into the ENROLLED_CLASSES table. No exceptions should be raised from the trigger.

   Create a new student, Martha Delan, with a student_level of 1 (freshman) and a new course, Anthropology 210, with a min_student_level of 2 (sophomore). Attempt to register the student for the course. The exception xSTUDENT_NOT_QUALIFIED should be raised from the trigger.

   Create a new student, Andrea Jones, with a student_level of 1 (freshman) and a new course, Arts & Culture 100, without a min_student_level . Attempt to register the student for the course. No exceptions should be raised from the trigger.

At this point, we have created three new classes and three new students, and successfully registered two of the three new students for a class. Now, we have to test the UPDATE functionality of the trigger. We can test the trigger s UPDATE functionality by using tests such as the following:

   Create a new course, Computer Science 560, with a min_student_level of 5 (master s student). Use an UPDATE statement to move student John Williams from Psychology 101 to Computer Science 560. The exception xSTUDENT_NOT_QUALIFIED should be raised from the trigger.

   Set the student_level for John Williams to 6 (doctoral). Use an UPDATE statement to move this student from Psychology 101 to Computer Science 560. No exceptions should be raised from the trigger.

Naturally, other triggers in place on the ENROLLED_CLASSES table might cause errors. If there are other triggers for the table, you should test your trigger alone first so that any internal errors can be isolated quickly. Once you are certain that the trigger s logic is sound, test the new trigger with the table s other triggers in place so you can resolve any conflicts among triggers.

Summary

Chapter 7 addresses the concept of database triggers and the most commonly encountered pitfall ”the mutating table error. At this point, you should have an understanding of a trigger s structure and how a trigger relates to a table. You should also have an understanding of the limitations placed on database triggers, and how to design and test your own triggers. Chapter 8 discusses debugging compile and runtime errors in stored PL/SQL objects.

Previous

Table of Contents

Next