How to disable Oracle triggers

What is trigger?

Triggers are stored programs that, when activated by specific database events such as INSERT, UPDATE, or DELETE statements, instantly perform (or discharge) the stored programs.

A trigger is made up of three components, as follows:

• An initiating event, which describes the database action that sets it off

• A trigger restriction, which further limits it

• A trigger action, which describes the program code to be run when the trigger goes off

Disable a single trigger

To disable a single trigger in Oracle, we can use the ALTER TRIGGER statement with the DISABLE option.

Syntax

Here’s the syntax for using the ALTER TRIGGER statement:

ALTER TRIGGER trigger_name DISABLE;

After the ALTER TRIGGER terms in this syntax, we must give the name of the trigger we wish to deactivate. For example, if we want to disable our user_trigger of the user table, we just simply replace trigger_name with user_trigger.

ALTER TRIGGER user_trigger DISABLE;

Once we execute this command, the trigger will be disabled and will not fire when the triggering event occurs.

Disable all the triggers of a table

To disable all the triggers of a table in Oracle, we can use the DISABLE ALL TRIGGERS command followed by the table name.

Syntax

Here’s the syntax for using the DISABLE ALL TRIGGERS statement:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Replace table_name with the name of the table for which we want to disable all the triggers. For instance, we can use the following sentence to remove all the triggers connected to the users table:

ALTER TABLE users DISABLE ALL TRIGGERS;

Once we execute this command, all the triggers associated with that specified table will be disabled and not fire when the triggering event occurs.

Enable the trigger

Use the ENABLE keyword in its place to re-enable the trigger.

Syntax

Here’s the syntax for using the ENABLE statement:

ALTER TRIGGER trigger_name ENABLE;

For example, if we want to enable our user_trigger of the user table, we simply replace trigger_name with user_trigger.

ALTER TRIGGER user_trigger ENABLE;

Once we execute this command, the trigger will be enabled.