Avoiding public embarrassment with triggers

August 21, 2015

1–2 minutes

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 Smile

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.

2 responses to “Avoiding public embarrassment with triggers”

Erik van Roon

March 20, 2018 at 7:10 pm

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.

Reply
connormcdonald

March 21, 2018 at 8:07 am

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.

Reply