Lab Objectives After this Lab, you will be able to: A database trigger is a named PL/SQL block stored in a database and executed implicitly when a triggering event occurs. An act of executing a trigger is referred to as firing a trigger. A triggering event is a DML (INSERT, UPDATE, or DELETE) statement executed against a database table. A trigger can fire before or after a triggering event. For example, if you have defined a trigger to fire before an INSERT statement on the STUDENT table, this trigger fires each time before you insert a row in the STUDENT table. The general syntax for creating a trigger is as follows (the reserved words and phrases surrounded by brackets are optional): CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} triggering_event ON table_name [FOR EACH ROW] [WHEN condition] DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END; The reserved word CREATE specifies that you are creating a new trigger. The reserved word REPLACE specifies that you are modifying an existing trigger. REPLACE is optional. However, note that both CREATE and REPLACE are present most of the time. Consider the following situation. You create a trigger as follows: CREATE TRIGGER trigger_name … In a few days you decide to modify this trigger. If you do not include the reserved word REPLACE in the CREATE clause of the trigger, an error message will be generated when you compile the trigger. The error message states that the name of your trigger is already used by another object. Once REPLACE is included in the CREATE clause of the trigger, there is less of a chance for an error because, if it is a new trigger, it is created, and if it is an old trigger, it is replaced. The trigger_name references the name of the trigger. BEFORE or AFTER specifies when the trigger fires (before or after the triggering event). The triggering_event references a DML statement issued against the table. The table_name is the name of the table associated with the trigger. The clause FOR EACH ROW specifies that a trigger is a row trigger and fires once for each row either inserted, updated, or deleted. You will encounter row and statement triggers in the next lab of this chapter. A WHEN clause specifies a condition that must evaluate to TRUE for the trigger to fire. For example, this condition may specify a certain restriction on the column of a table. Next, the trigger body is defined. It is important for you to realize that if you drop a table, the table's database triggers are dropped as well. You should be careful when using the reserved word REPLACE for a number of reasons. First, if you happen to use REPLACE and the name of an existing stored function, procedure, or package, it will be replaced by the trigger. Second, when you use the reserved word REPLACE and decide to associate a different table with your trigger, an error message is generated. For example, assume you created a trigger STUDENT_BI on the STUDENT table. Next, you decide to modify this trigger and associate it with the ENROLLMENT table. As a result, the following error message is generated: ERROR at line 1: ORA-04095: trigger 'STUDENT_BI' already exists on another table, cannot replace it Triggers are used for different purposes. Some uses for triggers are as follows: Enforcing complex business rules that cannot be defined by using integrity constraints Maintaining complex security rules Automatically generating values for derived columns Collecting statistical information on table accesses Preventing invalid transactions Providing value auditing The body of a trigger is a PL/SQL block. However, there are several restrictions that you need to know to create a trigger: A trigger may not issue a transactional control statement such as COMMIT, SAVEPOINT, or ROLLBACK. When the trigger fires, all operations performed become part of a transaction. When this transaction is committed or rolled back, the operations performed by the trigger are committed or rolled back as well. Any function or procedure called by a trigger may not issue a transactional control statement. It is not permissible to declare LONG or LONG RAW variables in the body of a trigger. Before Triggers Consider the following example of a trigger on the STUDENT table mentioned earlier in this chapter. This trigger fires before the INSERT statement on the STUDENT table and populates STUDENT_ID, CREATED_DATE, MODIFIED_DATE, CREATED_BY, and MODIFIED_BY columns. Column STUDENT_ID is populated with the number generated by the STUDENT_ID_SEQ sequence, and columns CREATED_DATE, MODIFIED_DATE, CREATED_USER, and MODIFIED_USER are populated with the current date and the current user name information, respectively. FOR EXAMPLE CREATE OR REPLACE TRIGGER student_bi BEFORE INSERT ON student FOR EACH ROW DECLARE v_student_id STUDENT.STUDENT_ID%TYPE; BEGIN SELECT STUDENT_ID_SEQ.NEXTVAL INTO v_student_id FROM dual; :NEW.student_id := v_student_id; :NEW.created_by := USER; :NEW.created_date := SYSDATE; :NEW.modified_by := USER; :NEW.modified_date := SYSDATE; END; This trigger fires before each INSERT statement on the STUDENT table. Notice that the name of the trigger is STUDENT_BI, where STUDENT references the name of the table on which the trigger is defined, and the letters BI mean BEFORE INSERT. There is no specific requirement for naming triggers; however, this approach to naming a trigger is descriptive because the name of the trigger contains the name of the table affected by the triggering event, the time of the triggering event (before or after), and the triggering event itself. In the body of the trigger, there is a pseudorecord, :NEW, allowing you to access a row currently being processed. In other words, a row is being inserted into the STUDENT table. The :NEW pseudorecord is of a type TRIGGERING_ TABLE%TYPE, so, in this case, it is of the STUDENT%TYPE type. In order to access individual members of the pseudorecord :NEW, dot notation is used. In other words, :NEW.CREATED_BY refers to the member, CREATED_BY, of the :NEW pseudorecord, and the name of the record is separated by the dot from the name of its member. Before you create this trigger, consider the following INSERT statement on the STUDENT table: INSERT INTO student (student_id, first_name, last_name, zip, registration_date, created_by, created_date, modified_by, modified_date) VALUES (STUDENT_ID_SEQ.NEXTVAL, 'John', 'Smith', '00914', SYSDATE, USER, SYSDATE, USER, SYSDATE); This INSERT statement contains values for the columns STUDENT_ID, CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE. It is important to note that for every row you insert into the STUDENT table, the values for these columns must be provided, and they are always derived in the same fashion. Why do you think the values for these columns must be provided when inserting a record into the STUDENT table? Once the trigger shown earlier is created, there is no need to include these columns in the INSERT statement, because the trigger will populate them with the required information. Therefore, the INSERT statement can be modified as follows: INSERT INTO student (first_name, last_name, zip, registration_date) VALUES ('John', 'Smith', '00914', SYSDATE); Notice that this version of the INSERT statement looks significantly shorter than the previous version. The columns STUDENT_ID, CREATED_BY, CREATED_DATE, MODIFIED_BY, and MODIFIED_DATE are not present. However, their values are provided by the trigger. As a result, there is no need to include them in the INSERT statement, and there is less of a chance for a transaction error. You should use BEFORE triggers in the following situations: When a trigger provides values for derived columns before an INSERT or UPDATE statement is completed. For example, the column FINAL_GRADE in the ENROLLMENT table holds the value of the student's final grade for a specific course. This value is calculated based on the student performance for the duration of the course. When a trigger determines whether an INSERT, UPDATE, or DELETE statement should be allowed to complete. For example, when you insert a record into the INSTRUCTOR table, a trigger can verify whether the value provided for the column ZIP is valid, or, in other words, if there is a record in the ZIPCODE table corresponding to the value of zip that you provided. After Triggers Assume there is a table called STATISTICS having the following structure: Name Null? Type ------------------------------- -------- ---- TABLE_NAME VARCHAR2(30) TRANSACTION_NAME VARCHAR2(10) TRANSACTION_USER VARCHAR2(30) TRANSACTION_DATE DATE This table is used to collect statistical information on different tables of the database. For example, you can record who deleted records from the INSTRUCTOR table and when they were deleted. Consider the following example of a trigger on the INSTRUCTOR table. This trigger fires after an UPDATE or DELETE statement is issued on the INSTRUCTOR table. FOR EXAMPLE CREATE OR REPLACE TRIGGER instructor_aud AFTER UPDATE OR DELETE ON INSTRUCTOR DECLARE v_type VARCHAR2(10); BEGIN IF UPDATING THEN v_type := 'UPDATE'; ELSIF DELETING THEN v_type := 'DELETE'; END IF; UPDATE statistics SET transaction_user = USER, transaction_date = SYSDATE WHERE table_name = 'INSTRUCTOR' AND transaction_name = v_type; IF SQL%NOTFOUND THEN INSERT INTO statistics VALUES ('INSTRUCTOR', v_type, USER, SYSDATE); END IF; END; This trigger fires after an UPDATE or DELETE statement on the INSTRUCTOR table. In the body of the trigger, there are two Boolean functions, UPDATING and DELETING. The function UPDATING evaluates to TRUE if an UPDATE statement is issued on the table, and the function DELETING evaluates to TRUE if a DELETE statement is issued on the table. There is another Boolean function called INSERTING. As you have probably guessed, this function evaluates to TRUE when an INSERT statement is issued against the table. This trigger updates a record or inserts a new record into the STATISTICS table when an UPDATE or DELETE operation is issued against the INSTRUCTOR table. First, the trigger determines the type of the DML statement issued against the INSTRUCTOR table. The type of the DML statement is determined with the help of the UPDATING and DELETING functions. Next, the trigger tries to update a record in the STATISTICS table where TABLE_NAME is equal to INSTRUCTOR and TRANSACTION_NAME is equal to the current transaction (UPDATE or DELETE). Then the status of the UPDATE statement is checked with the help of SQL%NOTFOUND constructor. The SQL%NOTFOUND constructor evaluates to TRUE if the update statement does not update any rows and FALSE otherwise. So if SQL%NOTFOUND evaluates to TRUE, a new record is added to the STATISTICS table. Once this trigger is created on the INSTRUCTOR table, any UPDATE or DELETE operation causes modification of old records or creation of new records in the STATISTICS table. Furthermore, you can enhance this trigger by calculating how many rows are updated or deleted from the INSTRUCTOR table. You should use AFTER triggers in the following situations: 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. ![graphics/intfig07.gif](/books/4/474/1/html/2/images/0130473200/graphics/intfig07.gif) | 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. | 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 Answersa) | 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 Answersa) | 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 knowand more than likely does not knowhow to operate sequences to populate the ID. | Lab 17.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 17.1. 1) | A trigger can fire for which of the following? _____ Before a triggering event _____ After a triggering event _____ Before or after a triggering event
| 2) | How is a trigger executed? _____ Explicitly when a triggering event occurs _____ Implicitly when a triggering event occurs
| 3) | In order for a trigger to fire, the WHEN condition must evaluate to which of the following? _____ True _____ False
| 4) | A BEFORE INSERT trigger fires for which of the following? _____ Before an UPDATE is issued against the triggering table _____ After an INSERT is issued against the triggering table _____ Before an INSERT is issued against the triggering table
| 5) | When a SELECT statement is issued against the triggering table, which of the following triggers fire? _____ BEFORE trigger _____ AFTER trigger _____ BEFORE trigger and AFTER trigger _____ Triggers are not fired at all.
| |