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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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?