Lab 17.3 Exercise Answers
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
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'
|
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.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. |
لطفا منتظر باشید ...
