3.4 Table Triggers A trigger is a special stored program attached to a table. A trigger is executed when the event on which it is based occurs. There is a distinct difference between triggers and other types of stored programs. A trigger is directly associated with a table and always includes the event for which an action will occur. Since execution, or "firing of the trigger," is controlled only by this event, triggers cannot be executed directly by a user. In contrast, stored programs can be executed directly by an authorized user . A trigger cannot be created unless the text of the command includes a table name . Consequently, if the table is dropped, the trigger will also be dropped automatically. 3.4.1 About Creating a Trigger The statement that creates a trigger includes both the triggering event and the table name. You can specify that a trigger is to be fired either before or after the event, followed by one or more actions such as INSERT, UPDATE, or DELETE. Within Oracle8, you can also specify the INSTEAD OF condition. That is used to solve the problem of updating through complex views. All triggers are based on modification events. Triggers can be quite useful from a security standpoint to track or prevent activities that change the data. For security and/or monitoring purposes, tables are frequently created with extra fields to capture the username, time, and modification action performed. These additional columns are intended to be used for auditing. However, such a scheme only retains the last event unless you take other precautions to preserve the history. The information is also available to any user with the SELECT privilege on the table. A trigger can be used instead of maintaining this auditing data within the table. The trigger would be set to execute before an INSERT, UPDATE, or DELETE for each row. The trigger body would write audit information to another table. This data could include the name of the table being modified, the date and time, the username, and any other pertinent information, including the actual data before and after the modification was made. The user performing the action does not have to have any privileges on the underlying "audit" table where this information is written. The execution of the trigger is transparent to the user and, since most triggers become memory-resident through frequent usage, their execution is quite fast. See Chapter 11, for an application that uses this approach to implement auditing of data at the column level. An example of a trigger to capture audit type information follows : CREATE OR REPLACE TRIGGER BIUDR_EMP BEFORE INSERT OR UPDATE OR DELETE ON employee FOR EACH ROW /* Capture the id of the user making the change, the type of action, and the date of the action. */ DECLARE tyact VARCHAR2(1); BEGIN IF INSERTING THEN tyact := 'I'; ELSIF updating THEN tyact := 'U'; ELSIF deleting THEN tyact := 'D'; END IF; INSERT INTO private_audit (id_of_user, action_performed, table_name, primary_key, date_of_action) VALUES (user, tyact, 'EMP', :old.employee_num, sysdate); END; /  | Although we show the trigger name with the action representation (BIUDR BEFORE INSERT UPDATE DELETE FOR EACH ROW) before the table name (EMP), many developers prefer to list the table name first and then the trigger actions for ease of maintainability. It doesn't matter how the trigger is named, but do use a standard naming convention. | | 3.4.2 How the Trigger Works In the previous example, the user does not have to have any privileges on the table (private_audit) to which the trigger is writing. The trigger executes with the privileges of the trigger owner (who does not have to be the table owner). Assuming that the trigger owner either owns the target table or has appropriate permissions, the required entries will be made and the user performing the data manipulation will never know that anything happened . Also, if the user issues a ROLLBACK command, the trigger action is also rolled back so no misleading entries are made in the private_audit table. 3.4.3 Naming Triggers Trigger names do not have to follow any special rules other than the usual "no blanks or special characters ." The naming convention used in the previous example, however, is recommended. This convention combines the table name (or a shortened version if the table name is very long) with "style" abbreviations for the triggering event and action. The style indicates what the trigger is supposed to do: "B" for before, "I" for insert, "U" for update, "D" for delete, "R" for by-row. This naming convention allows the events that fire the trigger to be determined from the name. 3.4.4 What Cannot Be Trapped by a Trigger Remember the actions that cause the trigger code to execute in the example? You will notice that SELECT is not mentioned. The exclusion of SELECT is not an oversight. A trigger cannot be written to execute on a SELECT statement. Triggers are usually used to enforce business rules and maintain data integrity during INSERT, UPDATE, and DELETE events. |
