| Lab Objectives After this Lab, you will be able to: 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. Lab 17.2 Exercises 17.2.1 Use Row and Statement Triggers In this exercise, you create a trigger that fires before an INSERT statement is issued against the COURSE table. Create the following trigger: -- ch17_2a.sql, version 1.0 CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW DECLARE v_course_no COURSE.COURSE_NO%TYPE; BEGIN SELECT COURSE_NO_SEQ.NEXTVAL INTO v_course_no FROM DUAL; :NEW.COURSE_NO := v_course_no; :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; END; Answer the following questions: | a) | What type of trigger is created on the COURSE table (row or statement)? Explain your answer. | | b) | Based on the answer you provided for question (a), explain why this particular type is chosen for the trigger. | | c) | When an INSERT statement is issued against the COURSE table, which actions are performed by the trigger? | | d) | Modify this trigger so that if there is a prerequisite course supplied at the time of the insert, its value is checked against the existing courses in the COURSE table. |
17.2.2 Use INSTEAD OF Triggers In this exercise, you create a view STUDENT_ADDRESS and an INSTEAD OF trigger that fires instead of an INSERT statement issued against the view. Create the following view: CREATE VIEW student_address AS SELECT s.student_id, s.first_name, s.last_name, s.street_address, z.city, z.state, z.zip FROM student s JOIN zipcode z ON (s.zip = z.zip); Note that the SELECT statement is written in the ANSI 1999 SQL standard. | | You will find detailed explanations and examples of the statements using 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. |
Create the following INSTEAD OF trigger: -- ch17_3a.sql, version 1.0 CREATE OR REPLACE TRIGGER student_address_ins INSTEAD OF INSERT ON student_address FOR EACH ROW BEGIN INSERT INTO STUDENT (student_id, first_name, last_name, street_address, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (:NEW.STUDENT_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.STREET_ADDRESS, :NEW.ZIP, SYSDATE, USER, SYSDATE, USER, SYSDATE); END; Issue the following INSERT statements: INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '10019'); INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '12345'); Answer the following questions: | a) | What output is produced after each INSERT statement is issued? | | b) | Explain why the second INSERT statement causes an error. | | c) | Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table and raises an error if there is no such value. | | d) | Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table. If there is no corresponding record in the ZIPCODE table, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table. |
Lab 17.2 Exercise Answers This section gives you some suggested answers to the questions in Lab 17.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 17.2.1 Answers| a) | What type of trigger is created on the COURSE table (row or statement)? Explain your answer. | | A1: | Answer: The trigger created on the COURSE table is a row trigger because the CREATE TRIGGER clause contains the statement FOR EACH ROW. It means this trigger fires every time a record is added to the COURSE table. | | b) | Based on the answer you provided for question (a), explain why this particular type is chosen for the trigger. | | A2: | Answer: This trigger is a row trigger because its operations depend on the data in the individual records. For example, for every record inserted into the COURSE table, the trigger calculates the value for the column COURSE_NO. All values in this column must be unique, because it is defined as a primary key. A row trigger guarantees every record added to the COURSE table has a unique number assigned to the COURSE_NO column. | | c) | When an INSERT statement is issued against the COURSE table, which actions are performed by the trigger? | | A3: | Answer: First, the trigger assigns a number derived from the sequence COURSE_ NO_SEQ to the variable v_course_no via the SELECT INTO statement. Second, the variable v_course_no is assigned to the field COURSE_NO of the :NEW pseudorecord. Finally, the values containing the current user's name and date are assigned to the fields CREATED_BY, MODIFIED_BY, CREATED_DATE, and MODIFIED_DATE of the :NEW pseudorecord. | | d) | Modify this trigger so that if there is a prerequisite course supplied at the time of the insert, its value is checked against the existing courses in the COURSE table. | | A4: | Answer: The trigger you created should look similar to the following trigger. All changes are shown in bold letters. -- ch17_2b.sql, version 2.0 CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW DECLARE v_course_no COURSE.COURSE_NO%TYPE; v_prerequisite COURSE.COURSE_NO%TYPE; BEGIN IF :NEW.PREREQUISITE IS NOT NULL THEN SELECT course_no INTO v_prerequisite FROM course WHERE course_no = :NEW.PREREQUISITE; END IF; SELECT COURSE_NO_SEQ.NEXTVAL INTO v_course_no FROM DUAL; :NEW.COURSE_NO := v_course_no; :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20002, 'Prerequisite is not valid!'); END; Notice that because the PREREQUISITE is not a required column, or, in other words, there is no NOT NULL constraint defined against it, the IF statement validates the existence of the incoming value. Next, the SELECT INTO statement validates that the prerequisite already exists in the COURSE table. If there is no record corresponding to the prerequisite course, the NO_DATA_FOUND exception is raised and the error message "Prerequisite is not valid!" is displayed on the screen. Once this version of the trigger is created, the INSERT statement INSERT INTO COURSE (description, cost, prerequisite) VALUES ('Test Course', 0, 999); causes the following error: INSERT INTO COURSE (description, cost, prerequisite) * ERROR at line 1: ORA-20002: Prerequisite is not valid! ORA-06512: at "STUDENT.COURSE_BI", line 21 ORA-04088: error during execution of trigger 'STUDENT.COURSE_BI' |
17.2.2 Answers| a) | What output is produced after each INSERT statement is issued? | | A1: | Answer: Your output should look similar to the following: INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '10019'); 1 row created. INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '12345'); VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', * ERROR at line 2: ORA-02291: integrity constraint (STUDENT.STU_ZIP_FK) violated - parent key not found ORA-06512: at "STUDENT.STUDENT_ADDRESS_INS", line 2 ORA-04088: error during execution of trigger 'STUDENT. STUDENT_ADDRESS_INS' | | b) | Explain why the second INSERT statement causes an error. | | A2: | Answer: The second INSERT statement causes an error because it violates the foreign key constraint on the STUDENT table. The value of the zipcode provided at the time of an insert does not have a corresponding record in the ZIPCODE table. The ZIP column of the STUDENT table has a foreign key constraint STU_ZIP_FK defined on it. It means that each time a record is inserted into the STUDENT table, the incoming value of zipcode is checked by the system in the ZIPCODE table. If there is a corresponding record, the INSERT statement against the STUDENT table does not cause errors. For example, the first INSERT statement is successful because the ZIPCODE table contains a record corresponding to the value of zip '10019'. The second insert statement causes an error because there is no record in the ZIPCODE table corresponding to the value of zip '12345'. | | c) | Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table and raises an error if there is no such value. | | A3: | Answer: Your trigger should look similar to the following trigger. All changes are shown in bold letters. -- ch17_3b.sql, version 2.0 CREATE OR REPLACE TRIGGER student_address_ins INSTEAD OF INSERT ON student_address FOR EACH ROW DECLARE v_zip VARCHAR2(5); BEGIN SELECT zip INTO v_zip FROM zipcode WHERE zip = :NEW.ZIP; INSERT INTO STUDENT (student_id, first_name, last_name, street_address, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (:NEW.STUDENT_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.STREET_ADDRESS, :NEW.ZIP, SYSDATE, USER, SYSDATE, USER, SYSDATE); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20002, 'Zip code is not valid!'); END; In this version of the trigger, the incoming value of zipcode is checked against the ZIPCODE table via the SELECT INTO statement. If the SELECT INTO statement does not return any rows, the NO_DATA_FOUND exception is raised and the error message stating 'Zip code is not valid!' is displayed on the screen. Once this trigger is created, the second INSERT statement produces the following output: INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '12345'); VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', * ERROR at line 2: ORA-20002: Zip code is not valid! ORA-06512: at "STUDENT.STUDENT_ADDRESS_INS", line 18 ORA-04088: error during execution of trigger 'STUDENT.STUDENT_ADDRESS_INS' | | d) | Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table. If there is no corresponding record in the ZIPCODE table, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table. | | A4: | Answer: Your trigger should look similar to the following trigger. All changes are shown in bold letters. -- ch17_3c.sql, version 3.0 CREATE OR REPLACE TRIGGER student_address_ins INSTEAD OF INSERT ON student_address FOR EACH ROW DECLARE v_zip VARCHAR2(5); BEGIN BEGIN SELECT zip INTO v_zip FROM zipcode WHERE zip = :NEW.ZIP; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO ZIPCODE (zip, city, state, created_by, created_date, modified_by, modified_date) VALUES (:NEW.ZIP, :NEW.CITY, :NEW.STATE, USER, SYSDATE, USER, SYSDATE); END; INSERT INTO STUDENT (student_id, first_name, last_name, street_address, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (:NEW.STUDENT_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.STREET_ADDRESS, :NEW.ZIP, SYSDATE, USER, SYSDATE, USER, SYSDATE); END; Just like in the previous version, the existence of the incoming value of zipcode is checked against the ZIPCODE table via the SELECT INTO statement. When a new value of zipcode is provided by the INSERT statement, the SELECT INTO statement does not return any rows. As a result, the NO_DATA_FOUND exception is raised and the INSERT statement against the ZIPCODE table is executed. Next, control is passed to the INSERT statement against the STUDENT table. It is important to realize that the SELECT INTO statement and the exception-handling section have been placed in the inner block. This placement ensures that once the exception NO_DATA_FOUND is raised the trigger does not terminate but proceeds with its normal execution. Once this trigger is created, the second INSERT statement completes successfully: INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '123 Main Street', 'New York', 'NY', '12345'); 1 row created. |
Lab 17.2 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 17.2. | 1) | How many times does a row trigger fire if a DML (INSERT, UPDATE, or DELETE) operation is issued against a table? _____ As many times as there are rows affected by the DML operation _____ Once per DML operation
| | 1) | How many times does a statement trigger fire if a DML (INSERT, UPDATE, or DELETE) operation is issued against a table? _____ As many times as there are rows affected by the DML operation _____ Once per DML operation
| | 3) | What does the statement FOR EACH ROW mean? _____ A trigger is a statement trigger. _____ A trigger is a row trigger.
| | 4) | INSTEAD OF triggers are defined on which of the following? _____ Table _____ View _____ None of the above
| | 5) | INSTEAD OF triggers must always be which of the following? _____ Statement trigger _____ Row trigger
|
|
