Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

SQL Statements (continued), 4 of 11


ALTER TRIGGER

Syntax


Purpose

To enable, disable, or compile a database trigger. For information on creating a trigger, see "CREATE TRIGGER". For information on dropping a trigger, see "DROP TRIGGER".


Note:

This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use the CREATE TRIGGER statement with OR REPLACE


Prerequisites

The trigger must be in your own schema or you must have ALTER ANY TRIGGER system privilege.

In addition, to alter a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

See Also:

"CREATE TRIGGER" for more information on triggers based on DATABASE

Keywords and Parameters

schema 

is the schema containing the trigger. If you omit schema, Oracle assumes the trigger is in your own schema.  

trigger 

is the name of the trigger to be altered.  

ENABLE 

enables the trigger. You can also use the ENABLE ALL TRIGGERS clause of ALTER TABLE to enable all triggers associated with a table. See "ALTER TABLE"

DISABLE 

disables the trigger. You can also use the DISABLE ALL TRIGGERS clause of ALTER TABLE to disable all triggers associated with a table. See "ALTER TABLE"

COMPILE 

explicitly compiles the trigger, whether it is valid or invalid. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.  

 

Oracle first recompiles objects upon which the trigger depends, if any of these objects are invalid. If Oracle recompiles the trigger successfully, the trigger becomes valid.

If recompiling the trigger results in compilation errors, then Oracle returns an error and the trigger remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS. For information on debugging procedures, see Oracle8i Application Developer's Guide - Fundamentals. For information on how Oracle maintains dependencies among schema objects, including remote objects, see Oracle8i Concepts

 

DEBUG 

instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. This clause can be used for normal triggers and for instead-of triggers.  

Examples

Consider a trigger named REORDER created on the INVENTORY table. The trigger is fired whenever an UPDATE statement reduces the number of a particular part on hand below the part's reorder point. The trigger inserts into a table of pending orders a row that contains the part number, a reorder quantity, and the current date.

When this trigger is created, Oracle enables it automatically. You can subsequently disable the trigger with the following statement:

ALTER TRIGGER reorder DISABLE;
 

When the trigger is disabled, Oracle does not fire the trigger when an UPDATE statement causes the part's inventory to fall below its reorder point.

After disabling the trigger, you can subsequently enable it with the following statement:

ALTER TRIGGER reorder ENABLE; 

After you reenable the trigger, Oracle fires the trigger whenever a part's inventory falls below its reorder point as a result of an UPDATE statement. It is possible that a part's inventory falls below its reorder point while the trigger was disabled. In that case, when you reenable the trigger, Oracle does not automatically fire the trigger for this part until another transaction further reduces the inventory.


Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index