17.3.1 Understand Mutating TablesIn this exercise, you modify a trigger that causes a mutating table error when an INSERT statement is issued against the ENROLLMENT table. Create the following trigger: -- ch17_4a.sql, version 1.0 CREATE OR REPLACE TRIGGER enrollment_biu BEFORE INSERT OR UPDATE ON enrollment FOR EACH ROW DECLARE v_total NUMBER; v_name VARCHAR2(30); BEGIN SELECT COUNT(*) INTO v_total FROM enrollment WHERE student_id = :NEW.STUDENT_ID; -- 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; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'This is not a valid student'); END; Issue the following INSERT and UPDATE statements: 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 (student_id, section_id, enroll_date, created_by, created_date, modified_by, modified_date) VALUES (407, 98, SYSDATE, USER, SYSDATE, USER, SYSDATE); UPDATE ENROLLMENT SET student_id = 404 WHERE student_id = 407; Answer the following questions:
|