 |
|
Determining the
Trigger's Level
Oracle Tips by
Burleson
|
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;
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
