How to Disable a trigger in oracle?

Triggers are generally used to enforce complex business rules, value based auditing and some times to maintain the referential integrity in distributed environment.But some time require to disable the triggers temporarily for example if we are going to upload a volume of data in a table quickly.


To disable a trigger in oracle we can use ALTER TRIGGER statement with DISABLE option and the syntax is:

ALTER TRIGGER trigger_name DISABLE;

For example we have a trigger  refund_before_insert on table refund. To disable this we can execute the following command:

ALTER TRIGGER  refund_before_insert DISABLE;

 Suppose you have more than one trigger on a table and the requirement is to disable all those then we can use the ALTER TABLE statement and the syntax is :

ALTER TABLE Table_name DISABLE ALL TRIGGERS;

For example if we have two triggers on table Refund then we can apply below command to disable all the triggers :

ALTER TABLE  Refund DISABLE ALL TRIGGERS;

Note : some of the database tools (like TOD, Sql Developer) are providing the GUI to disable triggers.