ALTER TRIGGER

You can use the ALTER TRIGGER statement to enable, disable, compile, or rename a trigger.

Notice

This statement will not modify the existing declaration or definition of a trigger. To redeclare or redefine a trigger, use the OR REPLACE clause in the CREATE TRIGGER statement.

Prerequisites

If the trigger is in the SYS schema, you must connect to the database with the SYSDBA role. Otherwise, the trigger must be located in your own schema, or you must have the ALTER ANY PROCEDURE system privilege.

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

Syntax

Syntax of alter_trigger:

ALTER TRIGGER [ schema. ] trigger_name
  { ENABLE | DISABLE }
 ;

Semantics

Examples

Example 1: Disable the del_new_region trigger.

A trigger named del_new_region is created in a table named regions in the SYS schema. Each time when a DELETE operation is performed on the regions table, the trigger is fired, and the records whose values of region_id are greater than 3 are moved to the reg_his table. After you create this trigger, the database automatically enables it. Then, you can use the following statement to disable the trigger:

ALTER TRIGGER del_new_region DISABLE;

Example 2: Enable the del_new_region trigger.

ALTER TRIGGER del_new_region ENABLE;

After the trigger is enabled, it fires each time when a DELETE operation is performed on the regions table.