 |
|
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. |
