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

Answer: The trigger created on the COURSE table is a row trigger because the CREATE TRIGGER clause contains the statement FOR EACH ROW. It means this trigger fires every time a record is added to the COURSE table.

Answer: This trigger is a row trigger because its operations depend on the data in the individual records. For example, for every record inserted into the COURSE table, the trigger calculates the value for the column COURSE_NO. All values in this column must be unique, because it is defined as a primary key. A row trigger guarantees every record added to the COURSE table has a unique number assigned to the COURSE_NO column.

Answer: First, the trigger assigns a number derived from the sequence COURSE_ NO_SEQ to the variable v_course_no

via the SELECT INTO statement. Second, the variable v_course_no

is assigned to the field COURSE_NO of the :NEW pseudorecord. Finally, the values containing the current user's name and date are assigned to the fields CREATED_BY, MODIFIED_BY, CREATED_DATE, and MODIFIED_DATE of the :NEW pseudorecord.

Answer: The trigger you created should look similar to the following trigger. All changes are shown in bold letters.

-- ch17_2b.sql, version 2.0
CREATE OR REPLACE TRIGGER course_bi
BEFORE INSERT ON COURSE
FOR EACH ROW
DECLARE
v_course_no COURSE.COURSE_NO%TYPE;
v_prerequisite COURSE.COURSE_NO%TYPE; 
BEGIN
IF :NEW.PREREQUISITE IS NOT NULL THEN 
SELECT course_no 
INTO v_prerequisite 
FROM course 
WHERE course_no = :NEW.PREREQUISITE; 
END IF; 
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;
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
RAISE_APPLICATION_ERROR 
(-20002, 'Prerequisite is not valid!'); 
END;

Answer: Your output should look similar to the following:

INSERT INTO student_address 
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', 
'123 Main Street', New York', 'NY', '10019'); 
1 row created. 
INSERT INTO student_address 
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', 
'123 Main Street', 'New York', 'NY', '12345'); 
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', 
'123 Main Street', 'New York', 
* 
ERROR at line 2: 
ORA-02291: integrity constraint (STUDENT.STU_ZIP_FK) 
violated - parent key not found 
ORA-06512: at "STUDENT.STUDENT_ADDRESS_INS", line 2 
ORA-04088: error during execution of trigger 'STUDENT. 
STUDENT_ADDRESS_INS' 

Answer: The second INSERT statement causes an error because it violates the foreign key constraint on the STUDENT table. The value of the zipcode provided at the time of an insert does not have a corresponding record in the ZIPCODE table.

Answer: Your trigger should look similar to the following trigger. All changes are shown in bold letters.

-- ch17_3b.sql, version 2.0
CREATE OR REPLACE TRIGGER student_address_ins
INSTEAD OF INSERT ON student_address
FOR EACH ROW
DECLARE 
v_zip VARCHAR2(5); 
BEGIN
SELECT zip 
INTO v_zip 
FROM zipcode 
WHERE zip = :NEW.ZIP; 
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);
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
RAISE_APPLICATION_ERROR 
(-20002, 'Zip code is not valid!'); 
END;

Answer: Your trigger should look similar to the following trigger. All changes are shown in bold letters.

-- ch17_3c.sql, version 3.0
CREATE OR REPLACE TRIGGER student_address_ins
INSTEAD OF INSERT ON student_address
FOR EACH ROW
DECLARE 
v_zip VARCHAR2(5); 
BEGIN
BEGIN 
SELECT zip 
INTO v_zip 
FROM zipcode 
WHERE zip = :NEW.ZIP; 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
INSERT INTO ZIPCODE 
(zip, city, state, created_by, created_date, modified_by,
modified_date) 
VALUES 
(:NEW.ZIP, :NEW.CITY, :NEW.STATE, USER, 
SYSDATE, USER, SYSDATE); 
END; 
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;