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

 

 

   
 

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.

  
 

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