Lab 17.1 Exercises

17.1.1 Understand What a Trigger Is

In this exercise, you need to determine the trigger firing event, its type, and so on, based on the CREATE clause of the trigger.

Consider the following CREATE clause:

 CREATE TRIGGER student_au AFTER UPDATE ON STUDENT FOR EACH ROW WHEN (NVL(NEW.ZIP, ' ') <> OLD.ZIP)  Trigger Body  

In the WHEN statement of the CREATE clause, there is a pseudorecord, :OLD, allowing you to access a row currently being processed . It is important for you to note that neither :NEW nor :OLD are prefixed by the colon (:) when they are used in the condition of the WHEN statement.

You are already familiar with the pseudorecord :NEW. The :OLD pseudorecord allows you to access the current information of the record being updated. In other words, it is information currently present in the STUDENT table for a specified record. The :NEW pseudorecord allows you to access the new information for the current record. In other words, :NEW indicates the updated values. For example, consider the following UPDATE statement:

 UPDATE student    SET zip = '01247'  WHERE zip = '02189'; 

The value "01247" of the ZIP column is a new value, and the trigger references it as :NEW.ZIP. The value "02189" in the ZIP column is the previous value and is referenced as :OLD.ZIP.

It is important for you to note that :OLD is undefined for INSERT statements and :NEW is undefined for DELETE statements. However, the PL/SQL compiler does not generate syntax errors when :OLD or :NEW is used in triggers where the triggering event is an INSERT or DELETE operation. In this case, the field values are set to NULL for :OLD and :NEW pseudorecords.

Answer the following questions:

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.
b)	If an update statement is issued on the STUDENT table, how many times does this trigger fire?
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?
d)	Why do you think there is a NVL function present in the WHEN statement of the CREATE clause?

17.1.2 Use BEFORE and AFTER Triggers

In this exercise, you create a trigger on the INSTRUCTOR table firing before an INSERT statement is issued against the table. The trigger determines the values for the columns CREATED_BY, MODIFIED_BY, CREATED_DATE, and MODIFIED_DATE. In addition, it determines if the value of zip provided by an INSERT statement is valid.

Create the following trigger:

 -- ch17_1a.sql, version 1.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;    SELECT 'Y'      INTO v_work_zip      FROM zipcode     WHERE zip = :NEW.ZIP; EXCEPTION    WHEN NO_DATA_FOUND THEN       RAISE_APPLICATION_ERROR          (-20001, 'Zip code is not valid!'); END; 

Answer the following questions:

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.
b)	Modify this trigger so that another error message is displayed when an INSERT statement is missing a value for the column ZIP.
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.