Lab 17.2 Exercises


17.2.1 Use ROW and STATEMENT Triggers

In this exercise, you create a trigger that fires before an INSERT statement is issued against the COURSE table.

Create the following trigger:

 
 -- ch17_2a.sql, version 1.0 CREATE OR REPLACE TRIGGER course_bi BEFORE INSERT ON COURSE FOR EACH ROW DECLARE    v_course_no COURSE.COURSE_NO%TYPE; BEGIN    SELECT COURSE_NO_SEQ.NEXTVAL      INTO v_course_no      FROM DUAL;    :NEW.COURSE_NO := v_course_no;    :NEW.CREATED_BY := USER;    :NEW.CREATED_DATE := SYSDATE;    :NEW.MODIFIED_BY := USER;    :NEW.MODIFIED_DATE := SYSDATE; END; 

Answer the following questions:

a)

What type of trigger is created on the COURSE table (row or statement)? Explain your answer.

b)

Based on the answer you provided for question (a), explain why this particular type is chosen for the trigger.

c)

When an INSERT statement is issued against the COURSE table, which actions are performed by the trigger?

d)

Modify this trigger so that if there is a prerequisite course supplied at the time of the insert, its value is checked against the existing courses in the COURSE table.


17.2.2 Use INSTEAD OF Triggers

In this exercise, you create a view STUDENT_ADDRESS and an INSTEAD OF trigger that fires instead of an INSERT statement issued against the view.

Create the following view:

 
 CREATE VIEW student_address AS    SELECT s.student_id, s.first_name, s.last_name, s.street_address, z.city, z.state, z.zip      FROM student s      JOIN zipcode z        ON (s.zip = z.zip); 

Note that the SELECT statement is written in the ANSI 1999 SQL standard.

graphics/trick_icon.gif

You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL.


Create the following INSTEAD OF trigger:

 
 -- ch17_3a.sql, version 1.0 CREATE OR REPLACE TRIGGER student_address_ins INSTEAD OF INSERT ON student_address FOR EACH ROW BEGIN    INSERT INTO STUDENT       (student_id, first_name, last_name, street_address, zip, registration_date, created_by, created_date, modified_by, modified_date)    VALUES       (:NEW.STUDENT_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.STREET_ADDRESS, :NEW.ZIP, SYSDATE, USER, SYSDATE, USER, SYSDATE); END; 

Issue the following INSERT statements:

 
 INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith',        '123 Main Street', 'New York', 'NY', '10019'); INSERT INTO student_address VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith',        '123 Main Street', 'New York', 'NY', '12345'); 

Answer the following questions:

a)

What output is produced after each INSERT statement is issued?

b)

Explain why the second INSERT statement causes an error.

c)

Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table and raises an error if there is no such value.

d)

Modify the trigger so that it checks the value of the zipcode provided by the INSERT statement against the ZIPCODE table. If there is no corresponding record in the ZIPCODE table, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT 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