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