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

 

 

   
 

Walking Around the Rules
Oracle Tips by Burleson
 

Enforcing some business rules requires data that is inevitably stored in a trigger’s associated table or in tables that have referential integrity constraints with the trigger’s associated table. Your best option for working around this problem is to move your processing to an after statement statement-level trigger.

For instance, let’s say that when we add a student to the STUDENTS table or modify a student’s GPA, we want to set a flag in the student’s record if the student has the highest GPA. Implementing this with a before statement row-level trigger would cause a mutating table error.

Instead of trying to use a row-level trigger to enforce this rule, we could create an after INSERT or UPDATE statement-level trigger that reads the STUDENTS table to determine the highest GPA and then sets the flag for the appropriate student(s). The trigger in Listing 7.8 shows how this is accomplished.

Listing 7.8 Referencing a trigger’s associated table using an after statement trigger.

CREATE OR REPLACE
TRIGGER STUDENTS_AIU
AFTER INSERT OR UPDATE OF overall_gpa
ON STUDENTS
 
DECLARE
   nHighestGPA   number;
 
BEGIN
   UPDATE STUDENTS
   SET    highest_gpa = 'N';
   SELECT max (overall_gpa)
   INTO   nHighestGPA
   FROM   STUDENTS;
 
   UPDATE STUDENTS
   SET    highest_gpa = 'Y'
   WHERE  overall_gpa = nHighestGpa;
END;
/

This approach works very well if your business rules require you to consider all the data in your table every time a modification is made. One concern that you should be aware of with this approach is performance. While this trigger enforces the business rules properly, without the proper indexes in place, this trigger will be a performance hog on all but the smallest of tables.

If your business rules only require certain rows to be touched when processing a new row, you may want to use another approach. This requires you to do one of two things:

  • Add a new column to your table to indicate which records have been changed. Then use an after statement statement-level trigger to process rows that are marked as changed.

  • Create a new table that stores the key values of rows in the trigger’s associated table. We’ll call this table a key value lookup table. Use your row-level trigger to store the key values of new or modified rows in the key value lookup table. Implement an after statement statement-level trigger to process the rows that match against the rows in the key value lookup table.

Of these two approaches, the second approach is probably the most performance effective because it utilizes existing indexes on your table. Listing 7.9 is an example of the triggers used in this approach.

Listing 7.9 Implementing a key value lookup scheme to avoid mutating table errors.

CREATE OR REPLACE
TRIGGER STUDENTS_ARU
AFTER UPDATE
ON STUDENTS
FOR EACH ROW
 
BEGIN
   INSERT
   INTO   TOUCHED_STUDENTS
          (ssn)
   VALUES (:new.ssn);
END STUDENTS_ARU;
/
 
CREATE OR REPLACE
TRIGGER STUDENTS_AU
AFTER UPDATE
ON STUDENTS
 
DECLARE
   CURSOR TouchedRecords_cur
   IS
   SELECT ssn
   FOR UPDATE
   FROM   TOUCHED_STUDENTS;
 
   iFailingClasses   integer := 0;
   iStudentSSN       integer := 0;
 
BEGIN
   FOR TouchedRecords_rec IN TouchedRecords_cur LOOP
      iStudentSSN := TouchedRecords_rec.ssn;
 
      SELECT count (*)
      INTO   iFailingClasses
      FROM   SCHEDULED_CLASSES
      WHERE  ssn          = TouchedRecords_rec.ssn
      AND    course_grade > 'D';
 
      IF (iFailingClasses > 1) THEN
         UPDATE STUDENTS
         SET    probation_flag = 'Y'
         WHERE  ssn = iStudentSSN;
      END IF;
 
      DELETE
      FROM   TOUCHED_STUDENTS
      WHERE  CURRENT OF TouchedRecords_cur;
   END LOOP;
END STUDENTS_AU;
/

These two triggers work together to determine if a student should be put on academic probation every time the student’s record is modified—the row-level trigger pitches, and the statement-level trigger catches.

The only significant drawback to this approach is the overhead incurred for each separate transaction through the use of the INSERT and DELETE statements. If this is a concern, consider implementing the logic for each trigger inside packaged procedures that share access to a PL/SQL table.

In this alternate approach, modified records are written to a global PL/SQL table inside a package by the row-level trigger. The after statement trigger can then reference the data inside the PL/SQL table without performing a read against the database, saving a considerable amount of processing time.

 

               
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