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.




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