Avoiding public embarrassment with triggers

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s