Enable and Disable Triggers

In Oracle, it is possible to enable or disable triggers by using the ALTER TRIGGER and ALTER TABLE commands.

• ALTER TRIGGER DISABLE;


• ALTER TRIGGER ENABLE;


• ALTER TABLE DISABLE ALL TRIGGERS;


• ALTER TABLE ENABLE ALL TRIGGERS;

Now n Oracle 11g, you can enable and disable the triggers at the time of creating. Mean we can create trigger in enabled or disabled state. By default it is in enabled state.

CREATE TABLE trigger_test (
id NUMBER,
description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_test_trg
BEFORE INSERT ON trigger_test
FOR EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_TEST_TRG will be Executed');
END;
/

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_test VALUES (1, 'Enabled');
TRIGGER_TEST_TRG will be Executed

1 row created.

SQL>

CREATE OR REPLACE TRIGGER trigger_test_trg
BEFORE INSERT ON trigger_test
FOR EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line('TRIGGER_TEST_TRG will be Executed');
END;
/

SQL> INSERT INTO trigger_test VALUES (2, 'Disabled');

1 row created.

SQL>