Lab 17.2 Exercise Answers
This section gives you some suggested answers to the questions in Lab 17.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.
17.2.1 Answers
| a) | What type of trigger is created on the COURSE table (row or statement)? Explain your answer. | A1:
| 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. | | b) | Based on the answer you provided for question (a), explain why this particular type is chosen for the trigger. | A2:
| 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. | | c) | When an INSERT statement is issued against the COURSE table, which actions are performed by the trigger? | A3:
| 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. | | 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. | A4:
| 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;
|
Notice that because the PREREQUISITE is not a required column, or, in other words, there is no NOT NULL constraint defined against it, the IF statement validates the existence of the incoming value. Next, the SELECT INTO statement validates that the prerequisite already exists in the COURSE table. If there is no record corresponding to the prerequisite course, the NO_DATA_FOUND exception is raised and the error message "Prerequisite is not valid!" is displayed on the screen.
Once this version of the trigger is created, the INSERT statement
INSERT INTO COURSE (description, cost, prerequisite)
VALUES ('Test Course', 0, 999);
causes the following error:
INSERT INTO COURSE (description, cost, prerequisite)
*
ERROR at line 1:
ORA-20002: Prerequisite is not valid!
ORA-06512: at "STUDENT.COURSE_BI", line 21
ORA-04088: error during execution of trigger 'STUDENT.COURSE_BI'
17.2.2 Answers
| a) | What output is produced after each INSERT statement is issued? | A1:
| 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'
| | b) | Explain why the second INSERT statement causes an error. | A2:
| 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. |
The ZIP column of the STUDENT table has a foreign key constraint STU_ZIP_FK defined on it. It means that each time a record is inserted into the STUDENT table, the incoming value of zipcode is checked by the system in the ZIPCODE table. If there is a corresponding record, the INSERT statement against the STUDENT table does not cause errors. For example, the first INSERT statement is successful because the ZIPCODE table contains a record corresponding to the value of zip '10019'. The second insert statement causes an error because there is no record in the ZIPCODE table corresponding to the value of zip '12345'.
| 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. | A3:
| 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;
|
In this version of the trigger, the incoming value of zipcode is checked against the ZIPCODE table via the SELECT INTO statement. If the SELECT INTO statement does not return any rows, the NO_DATA_FOUND exception is raised and the error message stating 'Zip code is not valid!' is displayed on the screen.
Once this trigger is created, the second INSERT statement produces the following output:
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-20002: Zip code is not valid!
ORA-06512: at "STUDENT.STUDENT_ADDRESS_INS", line 18
ORA-04088: error during execution of trigger
'STUDENT.STUDENT_ADDRESS_INS'
| 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. | A4:
| 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;
|
Just like in the previous version, the existence of the incoming value of zipcode is checked against the ZIPCODE table via the SELECT INTO statement. When a new value of zipcode is provided by the INSERT statement, the SELECT INTO statement does not return any rows. As a result, the NO_DATA_FOUND exception is raised and the INSERT statement against the ZIPCODE table is executed. Next, control is passed to the INSERT statement against the STUDENT table.
It is important to realize that the SELECT INTO statement and the exception-handling section have been placed in the inner block. This placement ensures that once the exception NO_DATA_FOUND is raised the trigger does not terminate but proceeds with its normal execution.
Once this trigger is created, the second INSERT statement completes successfully:
INSERT INTO student_address
VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith',
'123 Main Street', 'New York', 'NY', '12345');
1 row created.
|
No comments:
Post a Comment