Previous Table of Contents Next


Boolean Functions

Database triggers that fire for more than one type of DML statement can use several boolean functions for logical control. These boolean functions are listed in Table 7.2.

Table 7.2 The boolean functions used in database triggers.

Boolean Function Return Value
DELETING Returns TRUE if the trigger was fired because of a DELETE statement; otherwise returns FALSE.
INSERTING Returns TRUE if the trigger was fired because of an INSERT statement; otherwise returns FALSE.
UPDATING Returns TRUE if the trigger was fired because of an UPDATE statement; otherwise returns FALSE.

The use of these boolean functions is illustrated in Listing 7.3.

Listing 7.3 Using boolean functions in a database trigger.

CREATE OR REPLACE
TRIGGER STUDENTS_ARIU
AFTER INSERT OR UPDATE OF overall_gpa
ON STUDENTS
FOR EACH ROW

DECLARE
   xGPA_TOO_HIGH   EXCEPTION;

BEGIN
    IF UPDATING THEN
      IF (:new.overall_gpa > 4.0) THEN
         RAISE xGPA_TOO_HIGH;
      END IF;
   
      IF (:new.overall_gpa > 3.5) THEN
         INSERT
         INTO   DEANS_LIST_STUDENTS
                (ssn)
         VALUES (:new.ssn);

      ELSIF (:new.overall_gpa < 1.5) THEN
         INSERT
         INTO   STUDENTS_NEEDING_ASSISTANCE
                (ssn)
         VALUES (:new.ssn);
      END IF;

    ELSIF INSERTING THEN
      :new.overall_gpa := NULL;
   END IF;
END STUDENTS_ARIU;
/

In Listing 7.3, the trigger STUDENTS_ARIU uses the UPDATING boolean function to determine if a student’s record is being updated, and the INSERTING boolean function to determine if the row is for a new student.

Creating And Dropping Triggers

A database trigger is created in SQL*Plus using a CREATE TRIGGER command, like the one shown in Listing 7.4.

Listing 7.4 A sample CREATE TRIGGER command.

CREATE OR REPLACE
TRIGGER STUDENTS_ARIU
AFTER INSERT OR UPDATE OF overall_gpa
ON STUDENTS
FOR EACH ROW

DECLARE
   xGPA_TOO_HIGH   EXCEPTION;

BEGIN
   IF (:new.overall_gpa > 4.0) THEN
      RAISE xGPA_TOO_HIGH;
   END IF;

   IF (:new.overall_gpa > 3.5) THEN
      INSERT
      INTO   DEANS_LIST_STUDENTS
             (ssn)
      VALUES (:new.ssn);

   ELSIF (:new.overall_gpa < 1.5) THEN
      INSERT
      INTO   STUDENTS_NEEDING_ASSISTANCE
             (ssn)
      VALUES (:new.ssn);

   END IF;
END STUDENTS_ARIU;
/

Using OR REPLACE in the command instructs Oracle to discard an existing trigger of the same name if it exists. If a trigger of the same name already exists and OR REPLACE isn’t specified, a compile error will occur.

It’s unusual for stored PL/SQL objects, including triggers, to be compiled successfully on the first attempt. For information about resolving compilation errors, refer to Chapter 8.

Triggers can be dropped inside SQL*Plus by using a command such as:

DROP TRIGGER STUDENTS_ARIU;

Database triggers can also be created or dropped using Oracle’s Procedure Builder or one of several third-party editors.

Recompiling Database Triggers

Database triggers can be recompiled (even if you don’t have a handy copy of the trigger’s source code) using a command like this one:

ALTER TRIGGER STUDENTS_ARIU COMPILE;

In order to recompile a trigger using this command, you must either have the ALTER TRIGGER or ALTER ANY OBJECT privilege.

Disabling And Enabling Triggers

Sometimes, you’ll want to turn off a trigger so data can be loaded more quickly. This can be done by using the ALTER TRIGGER command in SQL*Plus, as illustrated here:

ALTER TRIGGER STUDENTS_ARIU DISABLE;

If there is more than one trigger on a table and you want to disable all the triggers in one fell swoop, you can use the ALTER TABLE command shown here instead:

ALTER TABLE STUDENTS DISABLE ALL TRIGGERS;

Remember that when a trigger is disabled, data that is loaded into the table isn’t processed by the trigger at all. The trigger doesn’t even fire. Consequently, if your triggers are validating data or enforcing complex business rules, you run some risks by disabling triggers. Re-enabling a trigger does not cause it to fire retroactively.

Database triggers can be turned on again by using the same commands with ENABLE substituted for DISABLE, as shown in the following:

ALTER TRIGGER STUDENTS_ARIU ENABLE;
ALTER TABLE STUDENTS ENABLE ALL TRIGGERS;

Naturally, executing these commands requires that you have the proper privileges (ALTER TRIGGER, ALTER TABLE, or ALTER ANY OBJECT).


Previous Table of Contents Next