You can use the ALTER TRIGGER statement to enable, disable, compile, or rename a trigger.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 schema of the SYS user, 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 TRIGGER system privilege.
In addition, to modify a trigger in the database, you must have the ADMINISTER DATABASE TRIGGER system privilege.
Syntax
The syntax of alter_trigger is as follows:
ALTER TRIGGER [ schema. ] trigger_name
COMPILE
|{ ENABLE | DISABLE }
;
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| alter_trigger | schema | The name of the schema where the trigger is located. The default value is your schema. |
| alter_trigger | trigger_name | The name of the trigger to be recompiled. |
| alter_trigger | [ ENABLE | DISABLE ] | Enables or disables a trigger. |
| alter_trigger | COMPILE | Recompiles the trigger regardless of whether the trigger is valid. If the trigger depends on an invalid object, the database recompiles the object. If the trigger recompilation succeeds, the trigger is validated. Otherwise, an error is returned and the trigger remains invalid. The execution environment of a trigger is determined when you create the trigger. During recompilation, the database removes all persistent switch settings of the compiler, reuses the environment on the session, and updates the system tables. |
Examples
Example 1: Disable the del_new_region trigger.
A trigger named del_new_region is created in a table named regions in the schema of the SYS user. 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.
Example 3: Recompile the del_new_region trigger.
ALTER TRIGGER del_new_region COMPILE;