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


 

HTML Text

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.

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.

  
 

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