Enable or disable a trigger

You can enable and disable triggers in some Procedural Language (PL) scenarios.

You can use the ALTER TRIGGER statement in conjunction with a DISABLE or an ENABLE clause to enable or disable a trigger.

Enable a trigger

Syntax

ALTER TRIGGER trigger_name ENABLE;

Examples

1. Enable the del_new_region trigger.

   ALTER TRIGGER del_new_region ENABLE;
   Query OK, 0 rows affected
   

2. View the status of the del_new_region trigger.

   obclient> SELECT TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,
   STATUS FROM user_triggers;
   +----------------+-----------------+------------------+-------------+------------+---------+
   | TRIGGER_NAME   | TRIGGER_TYPE    | TRIGGERING_EVENT | TABLE_OWNER | TABLE_NAME | STATUS  |
   +----------------+-----------------+------------------+-------------+------------+---------+
   | DEL_NEW_REGION | BEFORE EACH ROW | DELETE           | SYS         | REGIONS    | ENABLED |
   +----------------+-----------------+------------------+-------------+------------+---------+
   1 row in set
   

Disable a trigger

You may need to temporarily disable a trigger in the following scenarios:

• The trigger cannot be compiled because a database object that the trigger depends on does not exist.

• The logic of the trigger does not meet your business needs.

• You want to load, modify, or delete a large amount of data without executing any trigger.

You can enable a disabled trigger as needed.

Syntax

ALTER TRIGGER trigger_name DISABLE;

Examples

1. Disable the del_new_region trigger.

   ALTER TRIGGER del_new_region DISABLE;
   

2. View the status of the del_new_region trigger.

   obclient>SELECT TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,
   STATUS FROM user_triggers;
   +----------------+-----------------+------------------+-------------+------------+----------+
   | TRIGGER_NAME   | TRIGGER_TYPE    | TRIGGERING_EVENT | TABLE_OWNER | TABLE_NAME | STATUS   |
   +----------------+-----------------+------------------+-------------+------------+----------+
   | DEL_NEW_REGION | BEFORE EACH ROW | DELETE           | SYS         | REGIONS    | DISABLED |
   +----------------+-----------------+------------------+-------------+------------+----------+
   1 row in set