Writing Database Triggers 327
11
49: ‘Employees are limited to a max of two departments.’);
50: END IF;
51: END LOOP;
52: END;
53: /
54: Trigger created.
Notice in line 1 that the previous trigger is dropped. Be sure to do this. The
table-level before trigger in lines 3–9 is fired at the beginning of an
INSERT
or
UPDATE
statement. It calls a package procedure that initializes the list counter. The row-
level trigger, named
only_two_departments_2
(defined in lines 11–19), is fired for each
row added or changed. This trigger adds the primary key of each record to the list main-
tained in the package-level PL/SQL table. The third trigger, defined in lines 21–52, is the
one that does the actual validation work. It is fired after the
INSERT
or
UPDATE
statement
is complete. It loops through each new or changed record and checks to be sure that each
employee in question has a maximum of two department assignments.
Now that you have created these triggers and the
emp_dept_procs
package, you can exe-
cute the SQL statements shown in Listing 11.22 in order to demonstrate that it works.
L

NPUT
/
O
UTPUT
15 7982 ch11 11/30/99 1:12 PM Page 327
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23: (emp_id, dept_id) VALUES (404,405);
24: 1 row created.
25: INSERT INTO emp_dept
26: (emp_id, dept_id) VALUES (404,406);
27: 1 row created.
28: INSERT INTO emp_dept
29: (emp_id, dept_id) VALUES (403,406);
30: INSERT INTO emp_dept
31: *
32: ERROR at line 1:
33: ORA-20000: Employees are limited to a max of two departments.
34: ORA-06512: at “MY_READER.ONLY_TWO_DEPARTMENTS_3”, line 21
35: ORA-04088: error during execution of trigger
➥’MY_READER.ONLY_TWO_DEPARTMENTS_3’
36: UPDATE emp_dept
37: SET dept_id = 406
38: WHERE emp_id = 403 AND dept_id = 405;
39: 1 row updated.
40: UPDATE emp_dept
41: SET emp_id = 403
42: WHERE emp_id = 404
43: AND dept_id = 405;
44: update emp_dept
45: *

This chapter has been complex, but it gave you the chance to see and experiment with
triggers implementing several different types of functionality. To reiterate, some possible
uses for triggers are to enforce business rules, generate column values (Listing 11.3),
enhance security, and maintain a historical record (Listing 11.6). These are just the tip of
the iceberg. The possibilities are limited only by your creativity and imagination. You
have also learned about the mutating table error, the bane of many trigger writers, and
should now have a good understanding of how to work around it.
Q&A
Q If I am using a trigger to enforce a business rule or a referential integrity rule,
does this affect the records that predate creation of the trigger?
A No, it doesn’t, and that’s a good point to keep in mind. When you create a declara-
tive constraint, you are really making a statement about the data that must always
be true. You cannot create a constraint if data is present that violates that constraint.
Triggers, on the other hand, affect only records that have been inserted, updated, or
deleted after the trigger was created. For example, creating the triggers limiting an
employee to only two department assignments will do nothing about preexisting
cases where an employee has more than two assignments.
Q The inserts in Listing 11.18 (lines 16–27) did not generate a mutating table
error message, yet they did query the table. Why is this?
A Single-row inserts are an exception to the rule about querying the underlying table.
However, if the insert is one that could possibly create more than one row, for
example an
INSERT INTO emp_dept SELECT...
,the rule about not querying the
mutating table still applies.
The solution shown in Listings 11.20 and 11.21 will work when triggers only
need to query the mutating table. The problem gets more complex if you
need to update those rows. Updating records in the mutating table from a
trigger will fire off the very same set of triggers that will also try to use the
very same package-level PL/SQL table to build a list of affected records, thus

Workshop
Use the following sections to test your comprehension of this chapter and put what
you’ve learned into practice. You’ll find the answers to the quiz and exercises in
Appendix A, “Answers.”
Quiz
1. Which data manipulation statements can support triggers?
2. What are the four basic parts of a trigger?
3. In a trigger, what are the correlation names
:OLD
and
:NEW
used for?
330 Day 11
15 7982 ch11 11/30/99 1:12 PM Page 330
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing Database Triggers 331
11
4. What is the name of the system view that can be used to retrieve trigger
definitions?
5. What is a mutating table?
6. Name some possible uses for triggers.
Exercises
1. Write a set of triggers to maintain the
emp_name
and
dept_name
fields redundantly
in the
emp_dept
table so that you do not have to join with the employee and depart-

•How to define an Oracle object type.
•How to create an object table, and how to use PL/SQL to store objects in
that table.
16 7982 ch12 11/30/99 1:11 PM Page 333
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
•How to create an object column in a regular table, and then access the data in that
column from PL/SQL.
•How to write the
ORDER
and
MAP
methods used when comparing objects.
A Brief Primer on Object-Oriented
Programming
Let’s begin by reviewing the basics of object-oriented programming (OOP). There is
really no magic to OOP: It’s simply a way of organizing code and data within your pro-
grams, one that you can use to model your code to more closely match the real world.
There are three pillars of good object-oriented design:
• Encapsulation
•Inheritance
• Polymorphism
Each of these is described in more detail in the following sections, using as examples
some real-world objects that you interact with every day.
Encapsulation
The term encapsulation refers to the fact that each object takes care of itself. A
well-designed object has a clear and well-defined interface that is used to manip-
ulate the object. All the program code necessary to perform any function on the object is
contained within the object definition itself. Thus an object is completely self-contained
and can be dropped in anywhere you need it.
A classic, and often used, real-world example of objects is audio/video components. Say

ple device. You picked up the phone, listened for a dial tone, and dialed a number by
using a rotary dial. When pushbutton phones came out, the original functionality was
inherited, except for the dialing interface, which was replaced by buttons. Cordless
phones inherited this functionality, added a radio to the implementation, and added an
on/off switch to the handset interface so that the handset did not need to be returned to
the cradle after each call.
One big advantage of inheritance in the OOP world, which is not present in the physical
world, is that you can change the definition of a software object, and the change will
propagate through all objects of that type, all objects inherited from those objects, and so
forth. Imagine changing the definition of a telephone to include pushbutton dialing, and
as a result having all the rotary phones in the world suddenly transform themselves into
pushbutton phones. Of course that can’t be done, but the software equivalent of it can.
Polymorphism
Polymorphism enables different objects to have methods of the same name that
accomplish similar tasks but in different ways. Think back to the home entertain-
ment system example for a moment. Each of the components—the TV, the FM tuner, the
amplifier, and so forth—has an on button. Many components also have associated
remotes, each also with an on button. Each of these buttons can invoke different process-
es inside each piece of equipment. A TV remote, for example, has to send an infrared
beam of light to the TV set when the on button is pushed. Despite the fact that each on
button invokes a different sequence of events, each button is still labeled on. It would be
inconvenient if this were not the case. Consistent naming frees your mind from having to
remember specifically for each device how to turn it on. You quickly become conditioned
to pushing the on button, or flipping a switch to on, no matter what device you are using.
N
EW
T
ERM
N
EW

methods are implemented as functions and procedures in the object class definition.
Anything you want to do to an object should be implemented as a method. If you want to
compare two objects, you should implement a compare method. If you want to copy an
object, you should implement a copy method. An
employee
object class, for example,
might contain a method to calculate an employee’s yearly bonus based on pay type,
longevity with the firm, and so on.
Advantages of OOP Over Traditional Methods
Objects offer the opportunity for increased reliability because of their well-defined inter-
faces. Reuse is made easier because all necessary code and data are part of the object
definition; thus object classes can easily be added to programs as new functionality is
required. Because you can model real-world business objects, as well as encapsulate and
hide the details behind an object’s functionality, you can program at a higher level of
abstraction, minimizing the amount of detail you need to remember, which makes your
job as a developer much easier.
336 Day 12
N
EW
T
ERM
N
EW
T
ERM
N
EW
T
ERM
16 7982 ch12 11/30/99 1:11 PM Page 336

You should now have a good idea of what OOP is and how Oracle handles objects. It’s
time to get down to some practical examples. To begin, let’s define an object type for
employee addresses. Listing 12.1 shows one possible implementation.
16 7982 ch12 11/30/99 1:11 PM Page 337
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
L
ISTING
12.1
The
address
Object Type
1: CREATE OR REPLACE TYPE address AS OBJECT (
2: street_1 VARCHAR2(40),
3: street_2 VARCHAR2(40),
4: city VARCHAR2(40),
5: state_abbr VARCHAR2(2),
6: zip_code VARCHAR2(5),
7: phone_number VARCHAR2(10),
8: MEMBER PROCEDURE ChangeAddress (
9: st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10: state IN VARCHAR2, zip IN VARCHAR2),
11: MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12: MEMBER FUNCTION getCity RETURN VARCHAR2,
13: MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14: MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15: MEMBER FUNCTION getPhone RETURN VARCHAR2,
16: MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17: );
18: /
19

I
NPUT
16 7982 ch12 11/30/99 1:11 PM Page 338
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Oracle8i Objects for Object-Oriented Programming 339
12
49: BEGIN
50: IF line_no = 1 THEN
51: RETURN street_1;
52: ELSIF line_no = 2 THEN
53: RETURN street_2;
54: ELSE
55: RETURN ‘ ‘; --send back a blank.
56: END IF;
57: END;
58:
59: MEMBER FUNCTION getCity RETURN VARCHAR2 IS
60: BEGIN
61: RETURN city;
62: END;
63:
64: MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
65: BEGIN
66: RETURN state_abbr;
67: END;
68:
69: MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
70: BEGIN
71: RETURN zip_code;
72: END;

pragma,
pragma,
...
);
CREATE TYPE BODY type_name [IS | AS]
MEMBER [function_definition | procedure_definition];
MEMBER [function_definition | procedure_definition];
...
[MAP | ORDER] MEMBER function_definition;
END;
In this syntax, the parameters are as follows:
•
type_name
—The name of the object type that you are defining. This can be any
name you choose, but it must conform to Oracle’s naming rules. Names may be up
to 30 characters long, must begin with a letter, and thereafter may contain letters,
digits, underscores (_), pound signs (#), and dollar signs ($).
•
attribute_name
—The attribute can have any name you choose, and must conform
to the rules for naming variables. An object must have at least one attribute.
•
datatype
—This can be another object type or an Oracle datatype. The Oracle
datatypes
LONG
,
LONG RAW
,
NCHAR

procedure_definition
—This contains the code for a procedure.
340 Day 12
,
S
YNTAX
,
16 7982 ch12 11/30/99 1:11 PM Page 340
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Oracle8i Objects for Object-Oriented Programming 341
12
An object must contain at least one attribute, and may contain as many as a thousand.
Member functions and procedures are entirely optional, as are compiler directives (that
is, pragmas). The definition of a
MAP
function or an
ORDER
function is also optional, but if
present, only one type may be used.
MAP
and
ORDER
functions are discussed later in this
lesson, in the section “Comparing Objects.”
As mentioned previously, an object type is a database-level definition. After an object
type is defined in the database, it can be used to create object tables, to define table
columns that are themselves objects, or to declare object variables in PL/SQL blocks.
Constructor Methods
Each Oracle object type has a built-in constructor method that is used to create
an instance of that type. This method is responsible for initializing all the

Using Objects.”
16 7982 ch12 11/30/99 1:11 PM Page 341
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Accessor Methods
Accessor methods are used to return an object’s attributes, and by convention,
they usually begin with
get
. The implementation of the
address
object shown in
Listing 12.1 contains five accessor methods:
•
getStreet
•
getCity
•
getStateAbbr
•
getPostalCode
•
getPhone
In most cases, these simply return the attribute in question. The
getStreet
method does
a bit more: It returns a blank if an invalid street address line is requested.
At first glance, it might seem silly to use a function like
getStreet
when you could just
as easily reference the
street_1

names typically start with
set
.
The
ChangeAddress
method of the
address
object described previously, for example,
would be considered a mutator method. It could have been named
setAddress
to con-
form more closely to convention, but the name
ChangeAddress
was chosen because it is
more descriptive of the real-world event for which this method exists, and because in a
N
EW
T
ERM
16 7982 ch12 11/30/99 1:11 PM Page 342
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Oracle8i Objects for Object-Oriented Programming 343
12
real-life situation, changing an address might involve more than just setting a few attrib-
utes.
Instantiating and Using Objects
After you have defined an object type, you probably want to do something with it. To use
an object from within PL/SQL, you need to follow these steps:
1. Declare one or more variables in which the datatype is the object type you want to
use.

10: address_1 := address (‘2700 Peerless Road’,’Apt 1’,
11: ‘Cleveland’,’TN’,’37312’,’4235551212’);
12: address_2 := address_1;
13:
14: --Change address #1
15: address_1.ChangeAddress (‘2800 Peermore Road’,’Apt 99’,
16: ‘Detroit’,’MI’,’48823’);
17:
18: --Instantiate a second object.
19: address_3 := address (‘2700 Eaton Rapids Road’,’Lot 98’,
20: ‘Lansing’,’MI’,’48911’,’5173943551’);
I
NPUT
continues
16 7982 ch12 11/30/99 1:11 PM Page 343
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
21:
22: --Now print out the attributes from each object.
23: dbms_output.put_line(‘Attributes for address_1:’);
24: dbms_output.put_line(address_1.getStreet(1));
25: dbms_output.put_line(address_1.getStreet(2));
26: dbms_output.put_line(address_1.getCity
27: || ‘ ‘ || address_1.getStateAbbr
28: || ‘ ‘ || address_1.getPostalCode);
29: dbms_output.put_line(address_1.getPhone);
30:
31: dbms_output.put_line(‘-------------------------’);
32: dbms_output.put_line(‘Attributes for address_2:’);
33: dbms_output.put_line(address_2.getStreet(1));
34: dbms_output.put_line(address_2.getStreet(2));

Lansing MI 48911
5173943551
PL/SQL procedure successfully completed.
344 Day 12
L
ISTING
12.2
continued
O
UTPUT
16 7982 ch12 11/30/99 1:11 PM Page 344
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Oracle8i Objects for Object-Oriented Programming 345
12
Notice that in lines 4–6, three object variables are defined. They are of type
address
and are used to contain
address
objects. When first created, these
objects are considered to be null. Any calls to their member methods result in errors and
any reference to their attributes evaluates to null.
The first
address
object is instantiated in line 10. This is done by calling the constructor
function for the
address
object, and assigning the value returned to the object variable
address_1
. In line 12 a copy of this object is assigned to
address_2

the table in question. To create an address column in the employee table, you must first
execute the Data Definition Language (DDL) statement shown in Listing 12.3.
L
ISTING
12.3
Creating a Column for the
address
Object
1: ALTER TABLE employee
2: ADD (
3: home_address address
4: );
A
NALYSIS
I
NPUT
16 7982 ch12 11/30/99 1:11 PM Page 345
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This statement simply adds a column, which is named
home_address
,to the
employee table. The column type is given as
address
, which is a reference to the
object type defined earlier in this chapter. For any existing employee records, the object
is considered to be null.
Now that an address column exists in the employee table, you can create some employee
records and store each employee’s address, along with the other information. Listing 12.4
shows two different ways to do this.
L

26: FROM employee
27: WHERE home_address IS NOT null;
28:
EMP_ID EMP_NAME
--------- --------------------------------
HOME_ADDRESS(STREET_1,STREET_2, CITY, STATE_ABBR, ZIP_CODE, PHONE_NUMBER)
-------------------------------------------------------------------------
597 Matthew Higgenbottom
ADDRESS(‘101 Maple’, NULL, ‘Mio’, ‘MI’, ‘48640’, ‘5173943551’)
598 Raymond Gennick
ADDRESS(‘911 Pearl’, ‘Apt 2’, ‘Lewiston’, ‘MI’, ‘48645’, ‘5173363366’)
346 Day 12
A
NALYSIS
I
NPUT
/
O
UTPUT
16 7982 ch12 11/30/99 1:11 PM Page 346
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.