Modify a trigger

You can use the REPLACE TRIGGER statement to modify a trigger. This topic describes how to modify a trigger.

Syntax

To modify a trigger, you can use only the REPLACE TRIGGER statement, which is a DDL statement.

Generally, you can use the following syntax to create or modify a trigger:

CREATE OR REPLACE TRIGGER trigger_name ...

Note

If the value of trigger_name does not exist, the trigger is created. If the value of trigger_name exists, the trigger is modified.

Examples

1. View the trigger DEL_NEW_REGION.

   obclient> SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME='DEL_NEW_REGION'\G
   *************************** 1. row ***************************
   TRIGGER_BODY: TRIGGER del_new_region
         BEFORE DELETE ON regions
         FOR EACH ROW
         WHEN (old.region_id >3)
      BEGIN
         INSERT INTO reg_his(region_id , region_name )
              VALUES( :old.region_id, :old.region_name );
      END
   1 row in set
   

2. Modify the trigger DEL_NEW_REGION. Change the data deletion logic from region_id >3 to region_id >2 for the DEL_NEW_REGION trigger.

   client> CREATE OR REPLACE TRIGGER del_new_region
              BEFORE DELETE ON regions
              FOR EACH ROW
              WHEN (old.region_id >2)
           BEGIN
              INSERT INTO reg_his(region_id , region_name)
                   VALUES( :old.region_id, :old.region_name );
           END;
         /
   Query OK, 0 rows affected, 1 warning
   

3. View the DEL_NEW_REGION trigger again.

   client> CREATE OR REPLACE TRIGGER del_new_region
             BEFORE DELETE ON regions
              FOR EACH ROW
              WHEN (old.region_id>2)
           BEGIN
              INSERT INTO reg_his(region_id , region_name )
                   VALUES( :old.region_id, :old.region_name );
           END;
         /
   Query OK, 0 rows affected, 1 warning