Creating and Dropping
Oracle Tips by
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
AFTER INSERT OR UPDATE OF overall_gpa
FOR EACH ROW
IF (:new.overall_gpa > 4.0) THEN
IF (:new.overall_gpa > 3.5) THEN
ELSIF (:new.overall_gpa < 1.5) THEN
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
Database triggers can be turned on again by using the same commands
with ENABLE substituted for DISABLE, as shown in the
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.