Delete a trigger

You can use the DROP TRIGGER statement to drop a trigger from the database. This topic describes how to drop a trigger.

Prerequisites

• To drop a trigger in your schema, you must be the owner of the trigger or have the DROP ANY TRIGGER system privilege.

• To drop a trigger in other schemas, you must have the ADMINISTER DATABASE TRIGGER system privilege.

Syntax

DROP TRIGGER [ schema. ] trigger;

Parameters

Examples

1. View the del_new_region trigger.

   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. Drop the del_new_region trigger.

   obclient> DROP TRIGGER del_new_region;
   Query OK, 0 rows affected
   

3. View the del_new_region trigger again.

   obclient>  SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'DEL_NEW_REGION'\G
   ORA-00942: table or view 'INFORMATION_SCHEMA.TRIGGERS' does not exist