Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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:

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.

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: