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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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