Disabling Trigger at Creation time in Oracle 11g
Posted by decipherinfosys on February 26, 2008
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,
Let us create a trigger now.
CREATE OR REPLACE TRIGGER TRI_TEST
BEFORE INSERT ON TEST
FOR EACH ROW
:NEW.COL3 := SYSDATE;
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.
One Response to “Disabling Trigger at Creation time in Oracle 11g”
Sorry, the comment form is closed at this time.