If you create a trigger that does not compile, any part of your application that refers to the table with the invalid trigger is basically dead until that trigger becomes valid. Even if a DML on that table is not going affect any rows, you still wont be able to issue it. For example,
SQL> delete from T where 1=0; delete from T where 1=0 * ERROR at line 1: ORA-04098: trigger 'TRG' is invalid and failed re-validation
That’s a bad bad place for your application…and a bad bad place for your career
In 11g and above, you can avoid this using the DISABLE keyword every time you create a trigger
All triggers should be created as:
create or replace trigger MY_TRIGGER DISABLE begin ... end;
If the trigger is created and compiled successfully, then you can enable it.
alter trigger MY_TRIGGER enable
If the trigger for some unforeseen reason does not compile, it is disabled, and hence, the failed compilation will not break your application.
Make it a coding standard for your database developers.
That is imho a very bad advise.
That trigger is there for a reason.
First of all you should make sure the trigger compiles before (re)creating it in an environment where it matters.
But, true, something unexpected might cause the trigger to not compile.
In that case however, you don’t want anybody screwing up your data by doing dml without the trigger doing what it’s supposed to (checking data, insert/update/delete other data,…).
Although it’s a bad thing if parts of your application stop working due to an invalid trigger, it’s far worse if it appears to function without errors, but now your data gets corrupted.
If your trigger doesn’t compile, I think you’d *want* the tables and hence application to be unavailable untill you’ve fixed the problem.
Thanks for the feedback Erik.
I’ll also add that I’m working on the assumption here that some reasonable practices are in place, for example, at the end of an installation process there is an assessment of “are there invalid objects?” etc which determines whether installation is deemed a success or failure.
Here’s my rationale for the approach I’m suggesting. I’d contend there are 3 scenarios that would be most typical:
1) Adding a new trigger to a working/running application
If it doesn’t compile – the application is dead. And since the trigger was new, the application was working without out, and so *existing* functionality should be unaffected by the trigger. So “disabled” is a good security mechanism.
2) Adding a new trigger to a brand new applcation installation
Probably could go either way since it is not in use yet.
3) Replacing an existing trigger on a working application
I would add a new trigger (ie, new name) in a disabled state. If I am happy with the compilation, then I would disable the original, enable the new trigger, drop the old. If that tiny window is still a risky exposure point, then I can mark the table read only for the duration of transition. If even that is an exposure point, I’d consider EBR.