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

 

 

   
 

Step-By-Step: Creating a Trigger
Oracle Tips by Burleson
 

Let’s design a trigger from scratch now, based on a simple set of rules that the trigger must enforce. We’ll create a trigger that prevents a students from enrolling in courses, unless the student satisfies a minimum student level (freshman, sophomore, junior, senior, master’s, or doctoral).

Trigger Requirements

While the final functionality of the trigger is a rule of the university, the design of the trigger must be grounded in the trigger’s associated table and any relationships between the associated table and other tables. For each student’s classes, a row must exist in the ENROLLED_CLASSES table, which has this structure:

ssn                    NOT NULL   varchar2 (9)
course_number          NOT NULL   number   (5)
audit_flag                        varchar2 (1)

The ssn column in the ENROLLED_CLASSES table has a foreign key relationship to the ssn column in the STUDENTS table, as follows:

ssn                    NOT NULL   varchar2 (9)
first_name             NOT NULL   varchar2 (10)
last_name              NOT NULL   varchar2 (12)
street_address         NOT NULL   varchar2 (30)
apartment_number       NOT NULL   varchar2 (4)
city                   NOT NULL   varchar2 (30)
state_code             NOT NULL   varchar2 (2)
zip_code               NOT NULL   number   (5)
home_phone             NOT NULL   number   (10)
financing_num          NOT NULL   integer  (9)
student_level          NOT NULL   number   (1)
degree_plan_code                  number   (5)
overall_gpa                       number   (3, 2)
most_recent_gpa                   number   (3, 2)
middle_name                       varchar2 (10)

The course_number column in the ENROLLED_CLASSES table has a foreign key relationship with the SCHEDULED_COURSES table, as follows:

course_number          NOT NULL   number   (5)
course_credits         NOT NULL   number   (1)
course_hours           NOT NULL   number   (1)
course_time            NOT NULL   varchar2 (2)
course_location        NOT NULL   number   (5)
min_student_level                 number   (1)
credit_flag                       varchar2 (1)

This means that our trigger must retrieve information from two different places (the SCHEDULED_COURSES and the STUDENTS tables) to determine whether the student can enroll in the class.

Now that we’ve examined the data structures that we’ll have to deal with to implement the trigger, let’s move on and start examining how the trigger has to work.

 

               
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