This section gives you some suggested answers to the questions in Lab 17.3, 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.3 Answers a) | What output is produced after the INSERT and UPDATE statements are issued? | A1: | Answer: Your output should look as follows : INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (184, 98, SYSDATE, USER, SYSDATE, USER , SYSDATE); INSERT INTO ENROLLMENT * ERROR at line 1: ORA-20000: Student, Salewa Zuckerberg, is registered for 3 courses already ORA-06512: at "STUDENT.ENROLLMENT_BIU", line 17 ORA-04088: error during execution of trigger 'STUDENT.ENROLLMENT_BIU' INSERT INTO ENROLLMENT (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (407, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); 1 row created. UPDATE enrollment SET student_id = 404 WHERE student_id = 407; UPDATE enrollment * ERROR at line 1: ORA-04091: table STUDENT.ENROLLMENT is mutating, trigger/function may not see it ORA-06512: at "STUDENT.ENROLLMENT_BIU", line 5 ORA-04088: error during execution of trigger 'STUDENT. ENROLLMENT_BIU' | b) | Explain why two of the statements did not succeed. | A2: | Answer: The INSERT statement does not succeed because it tries to create a record in the ENROLLMENT table for a student that is already registered for three courses. | The IF statement -- check if the current student is enrolled into too many -- courses IF v_total >= 3 THEN SELECT first_name' 'last_name INTO v_name FROM student WHERE student_id = :NEW.STUDENT_ID; RAISE_APPLICATION_ERROR (-20000, 'Student, 'v_name ', is registered for 3 courses already'); END IF; in the body of the trigger evaluates to TRUE, and as a result the RAISE_APPLICATION_ERROR statement raises a user-defined exception. The UPDATE statement does not succeed, because a trigger tries to read data from the mutating table. The SELECT INTO SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = :NEW.STUDENT_ID; statement is issued against the ENROLLMENT table that is being modified and therefore is mutating. c) | Modify the trigger so that it does not cause a mutating table error when an UPDATE statement is issued against the ENROLLMENT table. | A3: | Answer: First, create a package to hold the student's ID and name as follows: CREATE OR REPLACE PACKAGE student_adm AS v_student_id student.student_id%TYPE; v_student_name varchar2(50); END; Next, the existing trigger, SECTION_BIU, is modified as follows: CREATE OR REPLACE TRIGGER enrollment_biu BEFORE INSERT OR UPDATE ON enrollment FOR EACH ROW BEGIN IF :NEW.STUDENT_ID IS NOT NULL THEN BEGIN student_adm.v_student_id := :NEW.STUDENT_ID; SELECT first_name' 'last_name INTO student_adm.v_student_name FROM student WHERE student_id = student_adm.v_student_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid student'); END; END IF; END; Finally, create a new statement-level trigger on the ENROLLMENT table as follows: CREATE OR REPLACE TRIGGER enrollment_aiu AFTER INSERT OR UPDATE ON enrollment DECLARE v_total INTEGER; BEGIN SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = student_adm.v_student_id; -- check if the current student is enrolled into too -- many courses IF v_total >= 3 THEN RAISE_APPLICATION_ERROR (-20000, 'Student, ' student_adm.v_student_name ', is registered for 3 courses already '); END IF; END; Once the package and two triggers are created, the UPDATE statement does not cause a mutating table error. | |