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 students 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 isnt specified, a compile error will occur.
Its 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 Oracles Procedure Builder or one of several third-party editors.
Database triggers can be recompiled (even if you dont have a handy copy of the triggers 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, youll 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 isnt processed by the trigger at all. The trigger doesnt 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 |