Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / Oracle DROP TRIGGER

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;

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.

Note that the trigger that you remove must be in your own schema or you must have the DROP ANY TRIGGER system privilege.

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;

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; /

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;

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

EXEC drop_trigger_if_exists('customers_credit_trg');

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');

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Enable Triggers
Next Mutating Table Error in Oracle

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.