| |
 |
|
Testing the Trigger
Oracle Tips by
Burleson
|
To ensure that mutating table errors are avoided, every test of a
database trigger should incorporate the following tasks:
-
Create a single new row in the trigger’s
associated table.
-
Create multiple new rows in the trigger’s
associated table.
-
Modify a single row in the trigger’s associated
table.
-
Modify multiple rows in the trigger’s
associated table.
-
Delete a single row in the trigger’s associated
table.
-
Delete multiple rows in the trigger’s
associated table.
Obviously, not all of these tests will be valid for a single
trigger, but one of the principles of testing is to test events that
shouldn’t cause a response. For instance, if you’ve created a
row-level INSERT trigger, executing an UPDATE statement
against the trigger’s associated table shouldn’t cause the trigger to
fire.
In addition to the previously outlined tests, a test for a database
trigger must also account for different sets of conditions that exist
in the data. Based on what we know about the trigger that we just
wrote, tests such as the following should be run to test the INSERT
functionality of the trigger:
-
Create a new student, John Williams, with a
student_level of 1 (freshman) and a new course, Psychology 101,
with a min_student_level of 1. Attempt to register the
student for the course by inserting a row into the
ENROLLED_CLASSES table. No exceptions should be raised from the
trigger.
-
Create a new student, Martha Delan, with a
student_level of 1 (freshman) and a new course, Anthropology
210, with a min_student_level of 2 (sophomore). Attempt to
register the student for the course. The exception
xSTUDENT_NOT_QUALIFIED should be raised from the trigger.
-
Create a new student, Andrea Jones, with a
student_level of 1 (freshman) and a new course, Arts & Culture
100, without a min_student_level. Attempt to register the
student for the course. No exceptions should be raised from the
trigger.
At this point, we have created three new classes and three new
students, and successfully registered two of the three new students
for a class. Now, we have to test the UPDATE functionality of
the trigger. We can test the trigger’s UPDATE functionality by
using tests such as the following:
-
Create a new course, Computer Science 560, with
a min_student_level of 5 (master’s student). Use an UPDATE
statement to move student John Williams from Psychology 101 to
Computer Science 560. The exception xSTUDENT_NOT_QUALIFIED
should be raised from the trigger.
-
Set the student_level for John Williams
to 6 (doctoral). Use an UPDATE statement to move this student
from Psychology 101 to Computer Science 560. No exceptions should be
raised from the trigger.
Naturally, other triggers in place on the ENROLLED_CLASSES
table might cause errors. If there are other triggers for the table,
you should test your trigger alone first so that any internal errors
can be isolated quickly. Once you are certain that the trigger’s logic
is sound, test the new trigger with the table’s other triggers in
place so you can resolve any conflicts among triggers.
Summary
Chapter 7 addresses the concept of database triggers and the most
commonly encountered pitfall—the mutating table error. At this point,
you should have an understanding of a trigger’s structure and how a
trigger relates to a table. You should also have an understanding of
the limitations placed on database triggers, and how to design and
test your own triggers. Chapter 8 discusses debugging compile and
runtime errors in stored PL/SQL objects.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
