INSTEAD OF Triggers

The INSTEAD OF INSERT trigger may be used to manipulate inserts, updates, or deletes against the view. The INSERT trigger is especially helpful when a view is based on several tables. The INSTEAD OF triggers act in a similar way to normal triggers. In the next code example, two triggers are created on the employee_names view.

The first trigger will insert directly into the employee table. It will also discard the value for the employee id that is in the INSERT statement. Instead, it will insert the next value from a sequence which has been defined to generate employee id numbers.

The second trigger will generate a user-defined error statement that scolds the user on an attempt to delete employees through this view. Although the permissions on the view often handle this type of activity, it is nice to be able to generate an error that is more descriptive than the usual “ORA-01031: insufficient privileges.”

CREATE OR REPLACE TRIGGER employee_names_trigger

INSTEAD OF INSERT ON employee_names

BEGIN

INSERT INTO employee VALUES

(employee_sequence.NEXTVAL, :NEW.f_name, :NEW.l_name, NULL, NULL,

 NULL, NULL);

END;



CREATE OR REPLACE TRIGGER employee_names_trigger_delete

INSTEAD OF DELETE ON employee_names

BEGIN

RAISE_APPLICATION_ERROR(-20000, ‘You may not delete employees through this

view!’);

END; 

---

NOTE:  
INSTEAD OF triggers are not limited to object views. They can be placed on normal views as well.

---

Large Objects

The previous sections have shown how object-oriented program design is beneficial for use with standard datatypes such as char, varchar, number, and date. However, many companies are looking toward object-oriented databases for the use of complex datatypes. Complex datatypes include pictures, video, spatial objects, and custom applications. Custom applications probably have the greatest potential in object-oriented databases.

For example, consider an architectural company that designs houses. The firm uses a specialized architectural design software package. This software will save blueprints into special files called .arc files. With current database technology, the .arc files can be stored in a LONG RAW datatype. Other columns in that table can hold descriptive columns about the .arc file. Unfortunately, these descriptive columns are not very beneficial to the architectural firm. It takes extra work to populate these columns, and they are limited in their ability. Figure 15.7 shows an example of files stored inside a relational database.

Figure 15.7.  The candidate table.

With object-oriented technology, the .arc files will be stored in new Oracle8 BLOB columns and accessed through object-oriented methods. These methods will probably be designed by the developer of the architectural software. The developer will focus on creating methods that can manipulate groups of .arc files as well as individual files. This functionality will allow the architectural firm to perform functions on a set of blueprints that cannot be performed if each blueprint is stored as a separate file on an operating system disk. One method may give the ability to query the architectural blueprints. An architect could ask the following questions:

•  How many windows were installed by our company in the last month?

•  How many houses have at least a three-car garage?

These questions cannot be answered if individual files are spread out on workstations. The analyst can also make changes to the set of blueprints. If the architectural firm receives a good deal on windows from a certain company, the analyst can execute a method that will change all the blueprint designs to use the new window where appropriate.

Another example is using the Oracle8 database to hold Microsoft Office documents. If all corporate office documents were stored in a central database, several functions could be performed.

•  Search Word documents for a reference to a particular product in the footer

•  Replace the company logo in all documents (Word, Excel, and PowerPoint)

•  Change the mileage reimbursement rate in Excel spreadsheet documents

Most of these files need to be stored in a large datatype. Oracle8 has four new datatypes for storage of large objects:

•  BLOB

•  CLOB

•  NCLOB

•  BFILE

They can hold up to 4GB of data. This size limit is sufficient for most objects. Some objects, such as a full-length movie, will not fit in 4GB of space. These objects must be split into several rows to fit into these new datatypes. These datatypes are greatly enhanced from the previous large datatypes (LONG and LONG RAW). These old datatypes (which are still supported) are limited in usability. Oracle corporation has lifted many of these restrictions with the new datatypes. Table 15.1 lists the four datatypes and their capabilities.