Lab 17.1 What Triggers Are

Lab Objectives

After this Lab, you will be able to:

Understand What a Trigger Is

Use BEFORE and AFTER Triggers

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  {BEFOREAFTER}  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.


 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.


 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:

  • When a trigger should fire after a DML statement is executed.

  • When a trigger performs actions not specified in a BEFORE trigger.

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: