Oracle Tips by
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
Because each row in the ENROLLED_CLASSES table represents a
single class, the trigger must fire for every row in the table.
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
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
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;
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
AFTER INSERT OR UPDATE
FOR EACH ROW
WHERE ssn = :new.ssn;
SELECT nvl (min_student_level, 0)
WHERE course_number = :new.course_number;
IF (iStudentLevel < iCourseLevel) THEN
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.