Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Lab 17.3 Exercises

17.3.1 Understand Mutating Tables

In 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: