Lab 17.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 17.1, 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.1.1 Answers

a)	Assume a trigger named STUDENT_AU already exists in the database. If you use the CREATE clause to modify the existing trigger, what error message is generated? Explain your answer.
A1:	Answer: An error message stating STUDENT_AU name is already used by another object is displayed on the screen. The CREATE clause has the ability to create new objects in the database, but it is unable to handle modifications. In order to modify the existing trigger, the REPLACE statement must be added to the CREATE clause. In this case, the old version of the trigger is dropped without warning, and the new version of the trigger is created.
b)	If an update statement is issued on the STUDENT table, how many times does this trigger fire?
A2:	Answer: The trigger fires as many times as there are rows affected by the triggering event, because the FOR EACH ROW statement is present in the CREATE trigger clause. When the FOR EACH ROW statement is not present in the CREATE trigger clause, the trigger fires once for the triggering event. In this case, if the following UPDATE statement UPDATE student SET zip = '01247' WHERE zip = '02189'; is issued against the STUDENT table, it updates 10 records and the trigger fires only once instead of 10 times.
c)	How many times does this trigger fire if an update statement is issued against the STUDENT table, but the ZIP column is not changed?
A3:	Answer: The trigger does not fire, because the condition of the WHEN statement evaluates to FALSE. The condition (NVL(NEW.ZIP, ' ') <> OLD.ZIP) of the WHEN statement compares the new value of zipcode to the old value of zipcode. If the value of the zipcode is not changed, this condition evaluates to FALSE. As a result, this trigger does not fire if an UPDATE statement does not modify the value of zipcode for a specified record.
d)	Why do you think there is a NVL function present in the WHEN statement of the CREATE clause?
A4:	Answer: If an UPDATE statement does not modify the column ZIP, the value of the field NEW.ZIP is undefined. In other words, it is NULL. A NULL value of ZIP cannot be compared with a non-NULL value of ZIP. Therefore, the NVL function is present in the WHEN condition. Because the column ZIP has a NOT NULL constraint defined, there is no need to use the NVL function for the OLD.ZIP field. For an UPDATE statement issued against the STUDENT table, there is always a value of ZIP currently present in the table.

17.1.2 Answers

a)	If an INSERT statement issued against the INSTRUCTOR table is missing a value for the column ZIP, does the trigger raise an exception? Explain your answer.
A1:	Answer: Yes, the trigger raises an exception. When an INSERT statement does not provide a value for the column ZIP, the value of the :NEW.ZIP is NULL. This value is used in the WHERE clause of the SELECT INTO statement. As a result, the SELECT INTO statement is unable to return data. Therefore, the exception NO_DATA_FOUND is raised by the trigger.
b)	Modify this trigger so that another error message is displayed when an INSERT statement is missing a value for the column ZIP.
A2:	Answer: Your script should look similar to the following script. All changes are shown in bold letters . -- ch17_1b.sql, version 2.0 CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER ; :NEW.MODIFIED_DATE := SYSDATE; IF :NEW.ZIP IS NULL THEN RAISE_APPLICATION_ERROR (-20002, 'Zip code is missing!'); ELSE SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END;

Notice that an IF-ELSE statement is added to the body of the trigger. This IF-ELSE statement evaluates the value of :NEW.ZIP. If the value of :NEW.ZIP is NULL, the IF-ELSE statement evaluates to TRUE, and another error message is displayed stating that the value of ZIP is missing. If the IF-ELSE statement evaluates to FALSE, the control is passed to the ELSE part of the statement, and the SELECT INTO statement is executed.

c)

Modify this trigger so there is no need to supply the value for the instructor's ID at the time of the INSERT statement.

A3:

Answer: Your version of the trigger should look similar to the one shown. All changes are shown in bold letters. -- ch17_1c.sql, version 3.0 CREATE OR REPLACE TRIGGER instructor_bi BEFORE INSERT ON INSTRUCTOR FOR EACH ROW DECLARE v_work_zip CHAR(1); v_instructor_id INSTRUCTOR.INSTRUCTOR_ID%TYPE; BEGIN :NEW.CREATED_BY := USER; :NEW.CREATED_DATE := SYSDATE; :NEW.MODIFIED_BY := USER; :NEW.MODIFIED_DATE := SYSDATE; SELECT 'Y' INTO v_work_zip FROM zipcode WHERE zip = :NEW.ZIP; SELECT INSTRUCTOR_ID_SEQ.NEXTVAL INTO v_instructor_id FROM dual; :NEW.INSTRUCTOR_ID := v_instructor_id; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20001, 'Zip code is not valid!'); END;

The original version of this trigger does not derive a value for the instructor's ID. Therefore, an INSERT statement issued against the INSTRUCTOR table has to populate the INSTRUCTOR_ID column as well. The new version of the trigger populates the value of the INSTRUCTOR_ID column, so that the INSERT statement does not have to do it.

Generally, it is a good idea to populate columns holding IDs in the trigger because when a user issues an INSERT statement, he or she might not know that an ID must be populated at the time of the insert. Furthermore, a user may not know ”and more than likely does not know ”how to operate sequences to populate the ID.