Oracle PL/SQL by Example- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
88
lượt xem
26
download

Oracle PL/SQL by Example- P7

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle pl/sql by example- p7', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL by Example- P7

  1. LAB 13.1 Lab 13.1 Exercises 272 ROLLBACK; SELECT * FROM statistics; TABLE_NAME TRANSACTIO TRANSACTION_USER TRANSACTI ----------- ---------- ---------------- --------- INSTRUCTOR UPDATE STUDENT 09-MAR-08 Notice that even though you roll the UPDATE statement against the INSTRUCTOR table, the record is inserted in the STATISTICS table due to the autonomous transaction specified in the trigger body. ▼ LAB 13.1 EXERCISES This section provides exercises and suggested answers, 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 and what the effects are of any different answers you may come up with. 13.1.1 Understand What a Trigger Is In this exercise, you need to determine the trigger firing event, its type, and so on based on the trigger’s CREATE clause. Consider the following CREATE clause: CREATE TRIGGER student_au AFTER UPDATE ON STUDENT FOR EACH ROW WHEN (NVL(NEW.ZIP, ' ') OLD.ZIP) Trigger Body... In the WHEN statement of the CREATE clause, the pseudorecord :OLD allows you to access a row currently being processed. It is important to note that neither :NEW nor :OLD is prefixed by a colon (:) when it is used in the condition of the WHEN statement. You are already familiar with the pseudorecord :NEW. The :OLD pseudorecord allows you to access the current information of the record being updated. In other words, it is information currently present in the STUDENT table for a specified record. The :NEW pseudorecord allows you to access the new information for the current record. In other words, :NEW indicates the updated values. For example, consider the following UPDATE statement: UPDATE student SET zip = '01247' WHERE zip = '02189'; The value 01247 of the ZIP column is a new value, and the trigger references it as :NEW.ZIP. The value 02189 in the ZIP column is the previous value and is referenced as :OLD.ZIP. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Lab 13.1 Exercises LAB 13.1 273 DID YOU KNOW? :OLD is undefined for INSERT statements, and :NEW is undefined for DELETE statements. However, the PL/SQL compiler does not generate syntax errors when :OLD or :NEW is used in triggers where the triggering event is an INSERT or DELETE operation, respectively. In this case, the field values are set to NULL for :OLD and :NEW pseudorecords. Answer the following questions: A) Assume that a trigger named STUDENT_AU already exists in the database. If you use the CREATE clause to modify the existing trigger, what error message is generated? Explain your answer. ANSWER: You see an error message stating that the STUDENT_AU name is already being used by another object. The CREATE clause can create new objects in the database, but it is unable to handle modifications. To modify the existing trigger, you must add the REPLACE statement to the CREATE clause. In this case, the old version of the trigger is dropped without warning, and the new version of the trigger is created. B) If an update statement is issued on the STUDENT table, how many times does this trigger fire? ANSWER: The trigger fires as many times as there are rows affected by the triggering event, because the FOR EACH ROW statement is present in the CREATE trigger clause. When the FOR EACH ROW statement is not present in the CREATE trigger clause, the trigger fires once for the triggering event. In this case, if the following UPDATE statement UPDATE student SET zip = '01247' WHERE zip = '02189'; is issued against the STUDENT table, it updates as many records as there are students with a zip code of 02189. C) How many times does this trigger fire if an update statement is issued against the STUDENT table but the ZIP column is not changed? ANSWER: The trigger does not fire, because the condition of the WHEN statement evaluates to FALSE. The condition (NVL(NEW.ZIP, ' ') OLD.ZIP) of the WHEN statement compares the new value of the zip code to the old value of the zip code. If the value of the zip code is not changed, this condition evaluates to FALSE. As a result, this trigger does not fire if an UPDATE statement does not modify the value of the zip code for a specified record. D) Why do you think an NVL function is present in the WHEN statement of the CREATE clause? ANSWER: If an UPDATE statement does not modify the column ZIP, the value of the field NEW.ZIP is undefined. In other words, it is NULL. A NULL value of ZIP cannot be compared with a non-NULL value of ZIP. Therefore, the NVL function is present in the WHEN condition. Because the column ZIP has a NOT NULL constraint defined, there is no need to use the NVL func- tion for the OLD.ZIP field. An UPDATE statement issued against the STUDENT table always has a value of ZIP present in the table. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. LAB 13.1 Lab 13.1 Exercises 274 13.1.2 Use BEFORE and AFTER Triggers In this exercise, you create a trigger on the INSTRUCTOR table that fires before an INSERT statement is issued against the table. The trigger determines the values for the columns CREATED_BY, MODIFIED_BY, CREATED_DATE, and MODIFIED_DATE. In addition, it determines if the value of zip provided by an INSERT statement is valid. Create the following trigger: -- ch13_1a.sql, version 1.0 CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END; Answer the following questions: A) If an INSERT statement issued against the INSTRUCTOR table is missing a value for the column ZIP, does the trigger raise an exception? Explain your answer. ANSWER: Yes, the trigger raises an exception. When an INSERT statement does not provide a value for the column ZIP, the value of :NEW.ZIP is NULL. This value is used in the WHERE clause of the SELECT INTO statement. As a result, the SELECT INTO statement is unable to return data. Therefore, the trigger raises a NO_DATA_FOUND exception. B) Modify this trigger so that another error message is displayed when an INSERT statement is missing a value for the column ZIP. ANSWER: The script should look similar to the following. All changes are shown in bold. -- ch13_1b.sql, version 2.0 CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. Lab 13.1 Exercises LAB 13.1 275 :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; IF :NEW.ZIP IS NULL THEN RAISE_APPLICATION_ERROR (-20002, 'Zip code is missing!'); ELSE SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END; Notice that an IF-ELSE statement is added to the body of the trigger. This IF-ELSE statement evalu- ates the value of :NEW.ZIP. If the value of :NEW.ZIP is NULL, the IF-ELSE statement evaluates to TRUE, and another error message is displayed, stating that the value of ZIP is missing. If the IF-ELSE statement evaluates to FALSE, control is passed to the ELSE part of the statement, and the SELECT INTO statement is executed. C) Modify this trigger so that there is no need to supply the value for the instructor’s ID at the time of the INSERT statement. ANSWER: The version of the trigger should look similar to the following. All changes are shown in bold. -- ch13_1c.sql, version 3.0 CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; :NEW.INSTRUCTOR_ID := INSTRUCTOR_ID_SEQ.NEXTVAL; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. LAB 13.1 Lab 13.1 Exercises 276 The original version of this trigger does not derive a value for the instructor’s ID. Therefore, an INSERT statement issued against the INSTRUCTOR table has to populate the INSTRUCTOR_ID column as well. The new version of the trigger populates the value of the INSTRUCTOR_ID column so that the INSERT statement does not have to do it. Generally, it is a good idea to populate columns holding IDs in the trigger, because when a user issues an INSERT statement, he or she might not know that an ID must be populated at the time of the insert. Furthermore, a user may not know—more than likely does not know—how to operate sequences to populate the ID. As mentioned previously, the ability to access a sequence via a PL/SQL expression is a new feature introduced in Oracle 11g. Prior to Oracle 11g, you needed to employ the SELECT INTO statement in the body of the trigger to populate the INSTRUCTOR_ID column. CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); v_instructor_id INSTRUCTOR.INSTRUCTOR_ID%TYPE; BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; SELECT INSTRUCTOR_ID_SEQ.NEXTVAL INTO v_instructor_id FROM dual; :NEW.INSTRUCTOR_ID := v_instructor_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. LAB 13.2 277 LAB 13.2 Types of Triggers LAB OBJECTIVES After completing this lab, you will be able to . Use row and statement triggers . Use INSTEAD OF triggers In the preceding lab 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. LAB 13.2 Types of Triggers 278 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 busi- ness 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 is 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 this error message: 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 a.m. and 5 p.m. If the day is during the business week but the time of day is not between 9 a.m. and 5 p.m., the error is generated. INSTEAD OF TRIGGERS So far you have seen triggers that are defined on 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 called a stored query. Consider the following example of the view created against the COURSE table: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. Types of Triggers LAB 13.2 279 FOR EXAMPLE You may find that you do not have privileges to create a view when logged in as STUDENT. If this is so, you need to log in as SYS and grant a CREATE VIEW privilege as follows: GRANT CREATE VIEW TO student; As soon as the privilege has been granted, the view on the COURSE table may be created as follows: CREATE VIEW course_cost AS SELECT course_no, description, cost FROM course; DID YOU KNOW? When 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 is 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; COMMIT; After 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. LAB 13.2 Types of Triggers 280 As mentioned earlier, some views are restricted as to whether they can be modified by INSERT, UPDATE, or DELETE statements. Specifically, these restrictions apply to the underlying SELECT statement, which is also called a view query. Thus, if a view query performs any of the opera- tions 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, and MINUS . Group functions such as AVG, COUNT, MAX, MIN, and SUM . GROUP BY or HAVING clauses . CONNECT BY or START WITH clauses . The DISTINCT operator . The ROWNUM pseudocolumn Consider the following view created on the INSTRUCTOR and SECTION tables: FOR EXAMPLE CREATE VIEW instructor_summary_view 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 0. BY THE WAY You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL standard in Appendix C,“ANSI SQL Standards,” and in the Oracle help. Throughout this book we try to provide 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. Types of Triggers LAB 13.2 281 This view is not updatable, because it contains the group function, COUNT(). As a result, the following DELETE statement DELETE FROM instructor_summary_view WHERE instructor_id = 109; causes the error shown: DELETE FROM instructor_summary_view * 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_view FOR EACH ROW BEGIN DELETE FROM instructor WHERE instructor_id = :OLD.INSTRUCTOR_ID; END; After the trigger is created, the DELETE statement against the INSTRUCTOR_SUMMARY_VIEW does not generate any errors: DELETE FROM instructor_summary_view 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 state- ment with a different instructor ID: DELETE FROM instructor_summary_view WHERE instructor_id = 101; When this DELETE statement is issued, it causes the error shown: DELETE FROM instructor_summary_view * ERROR at line 1: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. LAB 13.2 Types of Triggers 282 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 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 no record in the SECTION table corresponds 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, a corresponding record in the SECTION table, the child table, has 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 here: FOR EXAMPLE CREATE OR REPLACE TRIGGER instructor_summary_del INSTEAD OF DELETE ON instructor_summary_view 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_view WHERE instructor_id = 101; DELETE FROM instructor_summary_view * ERROR at line 1: ORA-02292: integrity constraint (STUDENT.GRTW_SECT_FK) violated - child record found Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. Lab 13.2 Exercises LAB 13.2 283 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 data- base, and the ripple effect that a particular design may introduce. This example suggests delet- ing 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 13.2 EXERCISES This section provides exercises and suggested answers, 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 and what the effects are of any different answers you may come up with. 13.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: -- ch13_2a.sql, version 1.0 CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW BEGIN :NEW.COURSE_NO := COURSE_NO_SEQ.NEXTVAL; :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; END; As mentioned, the ability to access sequence via a PL/SQL expression is a new feature introduced in Oracle 11g. Prior to Oracle 11g, you would have needed to employ the SELECT INTO statement in the body of the trigger to populate the COURSE_NO column. CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW DECLARE v_course_no COURSE.COURSE_NO%TYPE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. LAB 13.2 Lab 13.2 Exercises 284 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. ANSWER: The trigger created on the COURSE table is a row trigger because the CREATE TRIGGER clause contains the statement FOR EACH ROW. This means that this trigger fires every time a record is added to the COURSE table. B) Based on the answer you just provided, explain why this particular type is chosen for the trigger. ANSWER: This trigger is a row trigger because its operations depend on the data in the individ- ual 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 that 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 does the trigger perform? ANSWER: First, the trigger assigns a unique number derived from the sequence COURSE_NO_SEQ to the filed COURSE_NO_SEQ to the filed COURSE_NO OF THE :NEW PSEUDORECORD. Then, 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 a prerequisite course is supplied at the time of the insert, its value is checked against the existing courses in the COURSE table. ANSWER: The trigger you created should look similar to the following. All changes are shown in bold. -- ch13_2b.sql, version 2.0 CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW DECLARE 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. Lab 13.2 Exercises LAB 13.2 285 :NEW.COURSE_NO := COURSE_NO_SEQ.NEXTVAL; :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 PREREQUISITE is not a required column (in other words, no NOT NULL constraint is 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 no record corresponds to the prerequisite course, the NO_DATA_FOUND exception is raised, and the error message Prerequisite is not valid! is displayed on the screen. After 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' 13.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. BY THE WAY You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL standard in Appendix C and in the Oracle help. Throughout this book we try to provide examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. LAB 13.2 Lab 13.2 Exercises 286 Create the following INSTEAD OF trigger: -- ch13_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? ANSWER: The 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' Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. Lab 13.2 Exercises LAB 13.2 287 B) Explain why the second INSERT statement causes an error. ANSWER: The second INSERT statement causes an error because it violates the foreign key constraint on the STUDENT table. The value of the zip code 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. This means that each time a record is inserted into the STUDENT table, the system checks the incom- ing value of the zip code in the ZIPCODE table. If there is a corresponding record, the INSERT state- ment 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 code 10019. The second insert statement causes an error because no record in the ZIPCODE table corre- sponds to the value of zip code 12345. C) Modify the trigger so that it checks the value of the zip code provided by the INSERT statement against the ZIPCODE table and raises an error if there is no such value. ANSWER: The trigger should look similar to the following. All changes are shown in bold. -- ch13_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 zip code 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. LAB 13.2 Lab 13.2 Exercises 288 After 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 zip code provided by the INSERT statement against the ZIPCODE table. If the ZIPCODE table has no corresponding record, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table. ANSWER: The trigger should look similar to the following. All changes are shown in bold. -- ch13_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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. Lab 13.2 Exercises LAB 13.2 289 As in the previous version, the existence of the incoming value of the zip code is checked against the ZIPCODE table via the SELECT INTO statement. When a new value of zip code 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 after the exception NO_DATA_FOUND is raised, the trigger does not terminate but proceeds with its normal execution. After 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. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. 290 Try it Yourself ▼ TRY IT YOURSELF In this chapter you’ve learned about triggers. Here are some projects that will help you test the depth of your understanding: 1) Create or modify a trigger on the ENROLLMENT table that fires before an INSERT statement. Make sure that all columns that have NOT NULL and foreign key constraints defined on them are popu- lated with their proper values. 2) Create or modify a trigger on the SECTION table that fires before an UPDATE statement. Make sure that the trigger validates incoming values so that there are no constraint violation errors. The projects in this section are meant to have you use all the skills you have acquired throughout this chapter. The answers to these projects can be found in Appendix D and on this book’s companion Web site. Visit the Web site periodically to share and discuss your answers. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. CHAPTER 14 Compound Triggers CHAPTER OBJECTIVES In this chapter, you will learn about . Mutating table issues . Compound triggers In the preceding chapter, you explored the concept of triggers. You learned about using triggers in the database, events that cause triggers to fire, and differ- ent types of triggers. In this chapter, you will continue exploring triggers. You will learn about mutating table issues and how triggers can be used to resolve these issues. In Lab 14.1 you will see how to resolve mutating table issues in the Oracle data- base prior to version 11g. In Lab 14.2 you will learn about compound triggers, which were introduced in Oracle 11g, and how they can be used to resolve mutating table issues. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản