Previous | Table of Contents | Next |
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:
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.
When you create a trigger, Oracle stores the source code for the trigger within the data dictionary. This code is stored in the ALL_TRIGGERS view. The structure of the view is shown in Listing 7.10.
Listing 7.10 The structure of the ALL_TRIGGERS view.
owner varchar2 (30) trigger_name varchar2 (30) trigger_type varchar2 (16) triggering_event varchar2 (26) table_owner varchar2 (30) table_name varchar2 (30) referencing_names varchar2 (87) when_clause varchar2 (2000) status varchar2 (8) description varchar2 (2000) trigger_body long
If you re already familiar with the structure of the ALL_SOURCE view (the view that shows you where the source code for procedures, functions, and packages is located), you ll note one significant difference ”the source code for a trigger is stored in a long variable as a single string and not on a line-by-line basis like procedures, functions, and packages.
Previous | Table of Contents | Next |