Previous | Table of Contents | Next |
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.
| |
---|---|
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.
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.
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.
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 |