Oracle7 Server SQL Reference

ALTER TRIGGER

Purpose

To enable, disable, or compile a database trigger:

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the trigger's creation label or you must satisfy one of the following criteria:

If the trigger's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the trigger's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the trigger's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

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

trigger

is the name of the trigger to be altered.

ENABLE

enables the trigger.

DISABLE

disables the trigger.

COMPILE

compiles the trigger.

Usage Notes

You can use the ALTER TRIGGER command to explicitly recompile a trigger that is invalid. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.

When you issue an ALTER TRIGGER statement, Oracle7 recompiles the trigger regardless of whether it is valid or invalid.

When you recompile a trigger, Oracle7 first recompiles objects upon which the trigger depends, if any of these objects are invalid. If Oracle7 recompiles the trigger successfully, the trigger becomes valid. If recompiling the trigger results in compilation errors, then Oracle7 returns an error and the trigger remains invalid. You can then debug triggers using the predefined package DBMS_OUTPUT. For information on debugging procedures, see the "Using Procedures and Packages" chapter of the Oracle7 Server Application Developer's Guide. For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.

Note: This command does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, you must use the CREATE TRIGGER command with the OR REPLACE option.

Enabling and Disabling Triggers

A database trigger is always in one of the following states:

enabled

If a trigger is enabled, Oracle7 fires the trigger when a triggering statement is issued.

disabled

If the trigger is disabled, Oracle7 does not fire the trigger when a triggering statement is issued.

When you create a trigger, Oracle7 enables it automatically. You can use the ENABLE and DISABLE options of the ALTER TRIGGER command to enable and disable a trigger.

You can also use the ENABLE and DISABLE clauses of the ALTER TABLE command to enable and disable all triggers associated with a table.

Note: The ALTER TRIGGER command does not change the definition of an existing trigger. To redefine a trigger, you must use the CREATE TRIGGER command with the OR REPLACE option.

Example

Consider a trigger named REORDER created on the INVENTORY table that 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, Oracle7 enables it automatically. You can subsequently disable the trigger with the following statement:

ALTER TRIGGER reorder

 	DISABLE

When the trigger is disabled, Oracle7 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, Oracle7 fires the trigger whenever a part's inventory falls below its reorder point as a result of an UPDATE statement. Note that a part's inventory may have fallen below its reorder point while the trigger was disabled. When you reenable the trigger, Oracle7 does not automatically fire the trigger for this part.