The ALTER TRIGGER statement is used to enable, disable, compile, or rename a database trigger.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
Notice
This statement does not change the declaration or definition of existing triggers. 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 as SYSDBA. Otherwise, the trigger must be in the current user's schema, or the user must have the ALTER ANY TRIGGER system privilege.
Additionally, to change a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
Syntax
The syntax for alter_trigger is as follows:
ALTER TRIGGER [ schema. ] trigger_name
COMPILE
|{ ENABLE | DISABLE }
;
Semantics
| Syntax | Keywords or syntax nodes | Description |
|---|---|---|
| alter_trigger | schema | The name of the schema where the trigger is located. The default value is the current user's schema. |
| alter_trigger | trigger_name | The name of the trigger to be recompiled. |
| alter_trigger | [ ENABLE | DISABLE ] | Enables or disables the trigger. |
| alter_trigger | COMPILE | Recompiles the trigger, regardless of whether it is valid or invalid. If the objects on which the trigger depends are invalid, the database recompiles them. If the database successfully recompiles the trigger, the trigger becomes effective. Otherwise, the database returns an error, and the trigger remains invalid. The execution environment of the trigger is determined at creation time. During recompilation, the database removes all persistent compiler switch settings, reuses the session environment, and updates the system tables. |
Examples
Example 1: Disable the del_new_region trigger.
The SYS schema created a trigger named del_new_region on the regions table. Every time a DELETE operation is performed on the regions table, the trigger is triggered, moving records with region_id greater than 3 to the reg_his table. After creating this trigger, the database automatically enables it. 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 re-enabling the trigger, the database will trigger the trigger whenever a DELETE statement changes the regions table.
Example 3: Recompile the del_new_region trigger.
ALTER TRIGGER del_new_region COMPILE;