Disabling Trigger at Creation time in Oracle 11g

Prior to Oracle 11g, disabling triggers used to be a two step process. Triggers were automatically created in an ‘enabled’ state. To disable it we had to disable it using the DISABLE clause of the ‘ALTER TRIGGER’ or ‘ALTER TABLE ‘ commands.

In Oracle 11g, we can specify ‘DISABLE’ clause in trigger definition it self.  In the absence of the ‘DISABLE’ clause, the trigger is created in an ‘ENABLED’ state. Following is the script to create table and then create trigger in disabled state.

CREATE TABLE TEST
(
COL1 VARCHAR(5) NOT NULL,
COL2 NUMBER(5),
COL3 DATE
);

Let us create a trigger now.

CREATE OR REPLACE TRIGGER TRI_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
DISABLE
BEGIN
:NEW.COL3 := SYSDATE;
END;

Trigger will be created successfully.  We can verify the status of the trigger using data dictionary view user_triggers. Even though trigger is created in a disabled status,  Oracle will make sure that it does not have any compilation errors. Trigger will be compiled with errors if we try to reference non-existent column in the trigger.

This feature can be very useful when we want to enable trigger at later stage for a specific events and as mentioned earlier, we can avoid extra step of altering the trigger after creation.