17.2.1 Use ROW and STATEMENT TriggersIn 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:
17.2.2 Use INSTEAD OF TriggersIn 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.
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:
|