Lab 17.2 Types of Triggers

Lab Objectives

After this Lab, you will be able to:

Use Row and Statement Triggers

Use INSTEAD OF Triggers

In the previous lab of this chapter, you encountered the term row trigger. A row trigger is fired as many times as there are rows affected by the triggering statement. When the statement FOR EACH ROW is present in the CREATE TRIGGER clause, the trigger is a row trigger. Consider the following code:

FOR EXAMPLE

 CREATE OR REPLACE TRIGGER course_au AFTER UPDATE ON COURSE FOR EACH ROW ... 

In this code fragment, the statement FOR EACH ROW is present in the CREATE TRIGGER clause. Therefore, this trigger is a row trigger. If an UPDATE statement causes 20 records in the COURSE table to be modified, this trigger fires 20 times.

A statement trigger is fired once for the triggering statement. In other words, a statement trigger fires once, regardless of the number of rows affected by the triggering statement. To create a statement trigger, you omit the FOR EACH ROW in the CREATE TRIGGER clause. Consider the following code fragment:

FOR EXAMPLE

 CREATE OR REPLACE TRIGGER enrollment_ad AFTER DELETE ON ENROLLMENT ... 

This trigger fires once after a DELETE statement is issued against the ENROLLMENT table. Whether the DELETE statement removes one row or five rows from the ENROLLMENT table, this trigger fires only once.

Statement triggers should be used when the operations performed by the trigger do not depend on the data in the individual records. For example, if you want to limit access to a table to business hours only, a statement trigger is used. Consider the following example.

FOR EXAMPLE

 CREATE OR REPLACE TRIGGER instructor_biud BEFORE INSERT OR UPDATE OR DELETE ON INSTRUCTOR DECLARE    v_day VARCHAR2(10); BEGIN    v_day := RTRIM(TO_CHAR(SYSDATE, 'DAY'));    IF v_day LIKE ('S%') THEN       RAISE_APPLICATION_ERROR (-20000, 'A table cannot be '          'modified during off hours');    END IF; END; 

This is a statement trigger on the INSTRUCTOR table, and it fires before an INSERT, UPDATE, or DELETE statement is issued. First, the trigger determines the day of the week. If the day happens to be Saturday or Sunday, an error message is generated. When the following UPDATE statement on the INSTRUCTOR table is issued on Saturday or Sunday

 UPDATE instructor    SET zip = 10025  WHERE zip = 10015; 

the trigger generates the error message shown below:

  update INSTRUCTOR   *   ERROR at line 1:   ORA-20000: A table cannot be modified during off hours   ORA-06512: at "STUDENT.INSTRUCTOR_BIUD", line 6   ORA-04088: error during execution of trigger   'STUDENT.INSTRUCTOR_BIUD'  

Notice that this trigger checks for a specific day of the week. However, it does not check the time of day. You can create a more sophisticated trigger that checks what day of the week it is and if the current time is between 9:00 A.M. and 5:00 P.M . If the day falls on the business week and the time of the day is not between 9:00 A.M. and 5:00 P.M. , the error is generated.

Instead of Triggers

So far you have seen triggers that are defined on the database tables. PL/SQL provides another kind of trigger that is defined on database views. A view is a custom representation of data and can be referred to as a "stored query." Consider the following example of the view created against the COURSE table.

FOR EXAMPLE

 CREATE VIEW course_cost AS    SELECT course_no, description, cost      FROM course; 

It is important to note that once a view is created, it does not contain or store any data. The data is derived from the SELECT statement associated with the view. Based on the preceding example, the COURSE_COST view contains three columns that are selected from the COURSE table.

Similar to tables, views can be manipulated via INSERT, UPDATE, or DELETE statements, with some restrictions. However, it is important to note that when any of these statements are issued against a view, the corresponding data are modified in the underlying tables. For example, consider an UPDATE statement against the COURSE_COST view.

FOR EXAMPLE

 UPDATE course_cost    SET cost = 2000  WHERE course_no = 450; 

Once the UPDATE statement is executed, both SELECT statements against the COURSE_COST view and the COURSE table return the same value of the cost for course number 450.

 SELECT *   FROM course_cost  WHERE course_no = 450;  COURSE_NO  DESCRIPTION                    COST   ---------- ------------------------ ----------   450 DB Programming in Java         2000  SELECT course_no, cost   FROM course  WHERE course_no = 450;  COURSE_NO       COST   ---------- ----------  450       2000 

As mentioned earlier, there are restrictions placed on some views as to whether they can be modified by INSERT, UPDATE, or DELETE statements. Specifically, these restrictions apply to the underlying SELECT statement that is also referred to as a "view query." Thus, if a view query performs any of the operations or contains any of the following constructs, a view cannot be modified by an UPDATE, INSERT, or DELETE statement:

• Set operations such as UNION, UNION ALL, INTERSECT, MINUS

• Group functions such as AVG, COUNT, MAX, MIN, SUM

• GROUP BY or HAVING clauses

• CONNECT BY or START WITH clauses

• The DISTINCT operator

• ROWNUM pseudocolumn

FOR EXAMPLE

Consider the following view created on the INSTRUCTOR and SECTION tables:

 CREATE VIEW instructor_summary AS    SELECT i.instructor_id, COUNT(s.section_id) total_courses      FROM instructor i      LEFT OUTER JOIN section s        ON (i.instructor_id = s.instructor_id)    GROUP BY i.instructor_id; 

Note that the SELECT statement is written in the ANSI 1999 SQL standard. It uses the outer join between the INSTRUCTOR and SECTION tables. The LEFT OUTER JOIN indicates that an instructor record in the INSTRUCTOR table that does not have a corresponding record in the SECTION table is included in the result set with TOTAL_COURSES equal to zero.

You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL.

In the previous versions of Oracle, this statement would look as follows :

 SELECT i.instructor_id, COUNT(s.section_id) total_courses   FROM instructor i, section s  WHERE i.instructor_id = s.instructor_id (+) GROUP BY i.instructor_id; 

This view is not updatable because it contains the group function, COUNT(). As a result, the following DELETE statement

 DELETE FROM instructor_summary  WHERE instructor_id = 109; 

causes the error shown:

  DELETE FROM instructor_summary   *   ERROR at line 1:   ORA-01732: data manipulation operation not legal on this view  

You will recall that PL/SQL provides a special kind of trigger that can be defined on database views. This trigger is called an INSTEAD OF trigger and is created as a row trigger . An INSTEAD OF trigger fires instead of the triggering statement (INSERT, UPDATE, DELETE) that has been issued against a view and directly modifies the underlying tables.

Consider an INSTEAD OF trigger defined on the INSTRUCTOR_SUMMARY view created earlier. This trigger deletes a record from the INSTRUCTOR table for the corresponding value of the instructor's ID.

FOR EXAMPLE

 CREATE OR REPLACE TRIGGER instructor_summary_del INSTEAD OF DELETE ON instructor_summary FOR EACH ROW BEGIN    DELETE FROM instructor     WHERE instructor_id = :OLD.INSTRUCTOR_ID; END; 

Once the trigger is created, the DELETE statement against the INSTRUCTOR_ SUMMARY view does not generate any errors.

  DELETE FROM instructor_summary   WHERE instructor_id = 109;   1 row deleted.  

When the DELETE statement is issued, the trigger deletes a record from the INSTRUCTOR table corresponding to the specified value of INSTRUCTOR_ID. Consider the same DELETE statement with a different instructor ID:

 DELETE FROM instructor_summary  WHERE instructor_id = 101; 

When this DELETE statement is issued, it causes the error shown:

  DELETE FROM instructor_summary   *   ERROR at line 1:   ORA-02292: integrity constraint (STUDENT.SECT_INST_FK)   violated - child record found   ORA-06512: at "STUDENT.INSTRUCTOR_SUMMARY_DEL", line 2   ORA-04088: error during execution of trigger   'STUDENT.INSTRUCTOR_SUMMARY_DEL'  

The INSTRUCTOR_SUMMARY view joins the INSTRUCTOR and SECTION tables based on the INSTRUCTOR_ID column that is present in both tables. The INSTRUCTOR_ID column in the INSTRUCTOR table has is a primary key constraint defined on it. The INSTRUCTOR_ID column in the SECTION table has a foreign key constraint that references the INSTRUCTOR_ID column of the INSTRUCTOR table. Thus, the SECTION table is considered a child table of the INSTRUCTOR table.

The original DELETE statement does not cause any errors because there is no record in the SECTION table corresponding to the instructor ID of 109. In other words, the instructor with the ID of 109 does not teach any courses.

The second DELETE statement causes an error because the INSTEAD OF trigger tries to delete a record from the INSTRUCTOR table, the parent table. However, there is a corresponding record in the SECTION table, the child table, with the instructor ID of 101. This causes an integrity constraint violation error. It may seem that one more DELETE statement should be added to the INSTEAD OF trigger, as shown below.

 CREATE OR REPLACE TRIGGER instructor_summary_del INSTEAD OF DELETE ON instructor_summary FOR EACH ROW BEGIN  DELETE FROM section   WHERE instructor_id = :OLD.INSTRUCTOR_ID;  DELETE FROM instructor     WHERE instructor_id = :OLD.INSTRUCTOR_ID; END; 

Notice that the new DELETE statement removes records from the SECTION table before the INSTRUCTOR table because the SECTION table contains child records of the INSTRUCTOR table. However, the DELETE statement against the INSTRUCTOR_SUMMARY view causes another error:

 DELETE FROM instructor_summary  WHERE instructor_id = 101;  DELETE FROM instructor_summary   *   ERROR at line 1:   ORA-02292: integrity constraint (STUDENT.GRTW_SECT_FK)   violated - child record found   ORA-06512: at "STUDENT.INSTRUCTOR_SUMMARY_DEL", line 2   ORA-04088: error during execution of trigger   'STUDENT.INSTRUCTOR_SUMMARY_DEL'  

This time, the error refers to a different foreign key constraint that specifies the relationship between the SECTION and the GRADE_TYPE_WEIGHT tables. In this case, the child records are found in the GRADE_TYPE_WEIGHT table. This means that before deleting records from the SECTION table, the trigger must delete all corresponding records from the GRADE_TYPE_WEIGHT table. However, the GRADE_TYPE_WEIGHT table has child records in the GRADE table, so the trigger must delete records from the GRADE table first.

This example illustrates the complexity of designing an INSTEAD OF trigger. To design such a trigger, you must be aware of two important factors: the relationship among tables in the database, and the ripple effect that a particular design may introduce. This example suggests deleting records from four underlying tables. However, it is important to realize that those tables contain information that relates not only to the instructors and the sections they teach, but also to the students and the sections they are enrolled in.