{"id":2929,"date":"2019-08-13T00:22:31","date_gmt":"2019-08-13T07:22:31","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2929"},"modified":"2025-06-01T05:44:24","modified_gmt":"2025-06-01T12:44:24","slug":"oracle-disable-triggers","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/oracle-disable-triggers\/","title":{"rendered":"Oracle Disable Triggers"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to disable triggers of a table in the Oracle Database.<\/p>\n\n\n\n

Disable a single trigger #<\/a><\/h2>\n\n\n\n

Sometimes, you may want to disable a trigger<\/a> for testing and troubleshooting purposes. To disable a trigger, you use the ALTER TRIGGER DISABLE<\/code> statement:<\/p>\n\n\n

ALTER<\/span> TRIGGER<\/span> trigger_name DISABLE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the trigger that you want to disable after the ALTER TRIGGER<\/code> keywords.<\/p>\n\n\n\n
For example, to disable the trigger customers_audit_trigger<\/code> of the customers<\/code> table, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TRIGGER<\/span> customers_audit_trg DISABLE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Once a trigger is disabled, its color is gray if in the SQL Developer tool:<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
If don’t want to the ALTER TRIGGER<\/code> command, you can use SQL Developer tool to disable a trigger using these steps:<\/p>\n\n\n\n
First, right-click the trigger name and select Disable…<\/strong> menu item.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Second, click the Apply<\/strong> button in the dialog to disable the trigger.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Third, click the OK button in the confirmation dialog to acknowledge that the trigger has been disabled.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Disable all triggers of a table #<\/a><\/h2>\n\n\n\n
To disable all triggers associated with a table, you use the ATLER TABLE ... DISABLE ALL TRIGGERS<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name<\/span> DISABLE<\/span> ALL<\/span> TRIGGERS;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the table to which the triggers that you want to disable belong.<\/p>\n\n\n\n
For example, to disable all triggers associated with the customers<\/code> table, you use the following statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> customers DISABLE<\/span> ALL<\/span> TRIGGERS;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
All the triggers of the table customers<\/code> are disabled now. If you view them in SQL Developer, you will see that all of them are gray.<\/p>\n\n\n\n
\"oracle<\/figure>\n\n\n\n
Create a disabled trigger #<\/a><\/h2>\n\n\n\n
Sometimes, you may want to create a disabled trigger which is a trigger is the disabled state.<\/p>\n\n\n\n
For example, you want to create a trigger during the business hours and do not want to impact the current transactions.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To create a trigger in the disabled state, you use the CREATE TRIGGER<\/a><\/code> statement with the DISABLE<\/code> option:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> TRIGGER<\/span> trigger_name\n    BEFORE<\/span> | AFTER<\/span> event\n    FOR<\/span> EACH<\/span> ROW<\/span>\n    DISABLE<\/span> \n    WHEN<\/span> (condition)\ntrigger_body    \n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example creates a trigger on the table customers<\/code> in the disabled state:<\/p>\n\n\n
CREATE<\/span> OR REPLACE<\/span> TRIGGER<\/span> customers_bd_trg\n    BEFORE<\/span> DELETE<\/span>\n    ON<\/span> customers\n    FOR<\/span> EACH<\/span> ROW<\/span>\n    DISABLE<\/span>\nDECLARE<\/span>\n    l_order_count PLS_INTEGER;\nBEGIN<\/span>\n    -- check if the customer has a transaction<\/span>\n    SELECT<\/span> COUNT(*) INTO<\/span> l_order_count \n    FROM<\/span> orders\n    WHERE<\/span> customer_id = :OLD<\/span>.customer_id;\n    \n    -- raise an exception if the customer has at least one order <\/span>\n    IF<\/span> l_order_count > 0<\/span> THEN<\/span>\n        raise_application_error(-20010<\/span>,'Cannot delete customer '<\/span> || :OLD<\/span>.NAME || \n        ' because it already has transactions'<\/span>);\n    END<\/span> IF<\/span>;\nEND<\/span>;\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the ALTER TRIGGER trigger_name DISABLE<\/code> to disable a trigger.<\/li>\n\n\n\n
  • Use the ALTER TABLE table_name DISABLE ALL TRIGGERS<\/code> to disable all triggers of a table.<\/li>\n\n\n\n
  • Use the CREATE TRIGGER<\/code> statement with the DISABLE<\/code> option to create a disabled trigger.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t