From 11g, we have this in the documentation:
By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.So when I write my DDL scripts, given this behaviour, I know what I'd prefer:
SQL> create table test_table(a number); table TEST_TABLE created. create or replace trigger test_trigger_good before insert on test_table for each row DISABLE begin null -- missing semicolon end; / TRIGGER test_trigger_good compiled Warning: execution completed with warning SQL> insert into test_table (a) values (1); 1 rows inserted.or
SQL> create or replace trigger test_trigger_bad before insert on test_table for each row begin null -- missing semicolon end; / TRIGGER test_trigger_bad compiled Warning: execution completed with warning SQL> insert into test_table (a) values (1); SQL Error: ORA-04098: trigger 'DEVMGR.TEST_TRIGGER_BAD' is invalid and failed re-validation 04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation" *Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger. *Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.Only run this when you know things are cool, ie - the triggers does not have compilation errors.
alter trigger test_trigger_good enable;
Otherwise you get the same problem, since you can enable an invalid trigger.
Tip of the hat to Connor McDonald for suggesting this many moons ago.