Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter