views:

114

answers:

3

Some of the triggers in my database become invalid after certain changes on the tables. But it seems that they are still working. The only problem I have is if I use SQL Developer there are red crosses on the left hand side of the triggers indicating they are invalid. Is it a big issue?

I know I can recompile the trigger to fix that but I am not sure if this is really a issue worth to concern. If so I will need to review my previous hundreds of changes and find out what is causing the problem. Thank you.

A: 

Not a big issue at all.

Just right click on them to recompile and you're good to go... I'm writing this from my own experience.

If there are any errors with the code you've just changed they will appear so that you can fix it. The compiler will tell you where are the problems (line numbers, variable names, etc) in case of errors.

Leniel Macaferi
What if I don't even recompile the triggers? I am going to deploy the database to a dozen of clients' servers so it would be a pain to recompile every time.
newguy
But I think that recompiling is necessary. It can assure you that there are no errors with your database code. Time consuming but really really necessary. When they are marked with red crosses it means that the code the trigger references was changed someway. This is an alert so that you can take action recompiling it to assert that there aren't any errors originating from your code changes.
Leniel Macaferi
+6  A: 

Whenever we deploy a change to a database object any code which depends on it is invalidated. This affects triggers, views and stored procedures. However, the next time something calls that code the database will automatically recompile it.

So we don't need to worry about this, right? Well, yes, up to a point. The thing is, the invalidation of the triggers (or whatever) is a flag to us that a change has been made which could affect the operation of that trigger, which might have side-effects. The most obvious side-effect is that the trigger won't compile. More subtly, the trigger compiles but fails during operations.

Hence, it is a good idea to force the recompilation of triggers in a development environment, to ensure that our change has not fundamentally broken anything. But we can skip that step when we deploy our change in production, because we do so confident that everything will re-compile on demand. Depends on our nerve :)

Oracle provides mechanisms for automatically recompiling all the invalid objects in a schema.

  • The most straightforward is to use DBMS_UTILITY.COMPILE_SCHEMA(). But this has been dodgy since 8i (because support for Java Stored Procedures introduced the potential for circular dependencies) and is no longer guaranteed to compile all objects successfully first time.

  • In 9i Oracle gave us a script $ORACLE_HOME/rdbms/admin/utlrp.sql which recompiled things. Unfortunately it requires SYSDBA access.

  • In 10g they added the UTL_RECOMP package, which basically does everything that that script does. This is the recommended approach for recompiling large numbers of objects. Unfortunately it also requires SYSDBA access. Find out more.

In 11g Oracle introduced fine-grained dependency management. This means that changes to tables are evaluated at a finer granularity (basically column level rather than table level) , and only objects which are directly affected by the changes are affected. Find out more.

APC
Thanks, this explanation looks very nice. I clicked the link above to 10g's documentation and it says You must be connected AS SYSDBA to run this script.So looks like I have to login as SYSDBA anyway.
newguy
@Newguy - oops you're right.
APC
A: 

If the triggers are working, then it's likely Oracle is trapping an ORA-04068 error when it fires the trigger and retrying the trigger after it's been automatically recompiled.

DCookie
No, if you try to execute any trigger or stored proc that is marked "INVALID" Oracle will first attempt to recompile it before executing it. ORA-04068 has to do with package state and has nothing to do with the state of a trigger.
Jeffrey Kemp