Trigger is invalid in Oracle

oracle triggers recompile
newguy picture newguy · Jul 8, 2010 · Viewed 22.7k times · Source

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.

Answer

APC picture APC · Jul 8, 2010

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.

Related questions

PLSQL :NEW and :OLD

Can anyone help me understand when to use :NEW and :OLD in PLSQL blocks, I'm finding it very difficult to understand their usage.

Read More
ORA-04082: NEW or OLD references not allowed in table level triggers

itI have table named per. In the per table, I have a field named "fl1" and another field named "fl2". When updating a record, I want to check if the value of "fl1" has changed". If the value has changed, …

Read More
pragma autonomous_transaction in a trigger

I have written a trigger on one table which deletes data from other table upon a condition. The trigger has pragma autonomous_transaction, and trigger works as intended. However, I do wonder if there can be any problems in future, …

Read More