Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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.


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;


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.

   iStudentLevel            integer;
   iCourseLevel             integer;   
   SELECT student_level
   INTO   iStudentLevel
   WHERE  ssn = :new.ssn;
   SELECT nvl (min_student_level, 0)
   INTO   iCourseLevel
   WHERE  course_number = :new.course_number;
   IF (iStudentLevel < iCourseLevel) THEN
   END IF;

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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter