لطفا منتظر باشید ...
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:
| a) | What output is produced after the INSERT and UPDATE statements are issued? |
| b) | Explain why two of the statements did not succeed. |
| c) | Modify the trigger so that it does not cause a mutating table error when an UPDATE statement is issued against the ENROLLMENT table. |
