 |
|
Creating and Dropping
Triggers
Oracle Tips by
Burleson
|
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).
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
