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 Disable Triggers

Oracle Disable Triggers

Summary: in this tutorial, you will learn how to disable triggers of a table in the Oracle Database.

Disable a single trigger

Sometimes, you may want to disable a trigger for testing and troubleshooting purposes. To disable a trigger, you use the ALTER TRIGGER DISABLE statement:

ALTER TRIGGER trigger_name DISABLE;

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

For example, to disable the trigger customers_audit_trigger of the customers table, you use the following statement:

ALTER TRIGGER customers_audit_trg DISABLE;

Once a trigger is disabled, its color is gray if in the SQL Developer tool:

oracle disable trigger example

If don’t want to the ALTER TRIGGER command, you can use SQL Developer tool to disable a trigger using these steps:

First, right-click the trigger name and select Disable… menu item.

Second, click the Apply button in the dialog to disable the trigger.

Third, click the OK button in the confirmation dialog to acknowledge that the trigger has been disabled.

Disable all triggers of a table

To disable all triggers associated with a table, you use the ATLER TABLE ... DISABLE ALL TRIGGERS statement:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

In this syntax, you specify the name of the table to which the triggers that you want to disable belong.

For example, to disable all triggers associated with the customers table, you use the following statement:

ALTER TABLE customers DISABLE ALL TRIGGERS;

All the triggers of the table customers are disabled now. If you view them in SQL Developer, you will see that all of them are gray.

oracle disable all triggers example

Create a disabled trigger

Sometimes, you may want to create a disabled trigger which is a trigger is the disabled state.

For example, you want to create a trigger during the business hours and do not want to impact the current transactions.

To do it safely, you can create a trigger in the disabled state first. And then you enable it later during the maintenance hours or at the weekend.

To create a trigger in the disabled state, you use the CREATE TRIGGER statement with the DISABLE option:

CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER event FOR EACH ROW DISABLE WHEN (condition) trigger_body

This example creates a trigger on the table customers in the disabled state:

CREATE OR REPLACE TRIGGER customers_bd_trg BEFORE DELETE ON customers FOR EACH ROW DISABLE DECLARE l_order_count PLS_INTEGER; BEGIN -- check if the customer has a transaction SELECT COUNT(*) INTO l_order_count FROM orders WHERE customer_id = :OLD.customer_id; -- raise an exception if the customer has at least one order IF l_order_count > 0 THEN raise_application_error(-20010,'Cannot delete customer ' || :OLD.NAME || ' because it already has transactions'); END IF; END;

In this tutorial, you have learned how to use disable a trigger or all triggers of a table in the Oracle Database.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle INSTEAD OF Triggers
Next Oracle Enable Triggers

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.