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

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.




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net