Creating triggers, prior to 11g, would default them to an enabled state.
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.
