Oracle DROP TRIGGER

Summary: in this tutorial, you will learn how to use the Oracle DROP TRIGGER statement to remove a trigger from the database.

Introduction to the Oracle DROP TRIGGER statement

The DROP TRIGGER statement allows you to remove a trigger from the database.

Here is the basic syntax of the DROP TRIGGER statement:

DROP TRIGGER [schema_name.]trigger_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the trigger that you want to remove after the DROP TRIGGER keywords.

Optionally, you can specify the name of the schema to which the trigger belongs. If you skip the schema_name, Oracle will assume that the trigger is in your own schema.

If you attempt to remove a trigger that does not exist, Oracle will issue the error ORA-04080, indicating that the trigger does not exist.

Unlike other database systems like SQL Server and PostgreSQL, Oracle does not support IF EXISTS option to drop a trigger only if it exists. Therefore, the following syntax is not valid in Oracle:

DROP TRIGGER IF EXISTS trigger_name;
Code language: SQL (Structured Query Language) (sql)

Fortunately, you can develop a procedure that combines the DROP TRIGGER statement with dynamic SQL to drop a trigger only if it exists as follows:

CREATE OR REPLACE PROCEDURE drop_trigger_if_exists(
    in_trigger_name VARCHAR2
)
AS
    l_exist PLS_INTEGER;
BEGIN
    -- get the trigger count
    SELECT COUNT(*) INTO l_exist
    FROM user_triggers
    WHERE trigger_name = UPPER(in_trigger_name);
    
    -- if the trigger exist, drop it
    IF l_exist > 0 THEN 
        EXECUTE IMMEDIATE 'DROP TRIGGER ' ||  in_trigger_name;
    END IF;
END;
/
Code language: SQL (Structured Query Language) (sql)

In this procedure:

• First, get the number of triggers that matches the input trigger from the user_triggers data dictionary view using the COUNT() function.
• Then, use the EXECUTE IMMEDIATE statement to execute a dynamic SQL statement which removes the trigger.

Oracle DROP TRIGGER statement examples

The following statement drops the trigger customers_audit_trg of the customers table:

DROP TRIGGER customers_audit_trg;
Code language: SQL (Structured Query Language) (sql)

This example uses the procedure drop_trigger_if_exists to drop the trigger customers_credit_trg :

EXEC drop_trigger_if_exists('customers_credit_trg');
Code language: SQL (Structured Query Language) (sql)

And this example use the drop_trigger_if_exists procedure to remove a trigger that does not exist:

EXEC drop_trigger_if_exists('customers_xyz_trg');
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle DROP TRIGGER statement to remove a trigger from the database.