Re: Invalid Triggers

AHands_at_sprynet.com wrote:

> In article <fongdaEIw18C.DFp_at_netcom.com>,
> fongda_at_netcom.com wrote:
>
> > We've got instances in our database where a trigger has a status of
> > enabled in user_triggers and has a status of invalid in
> > user_objects...has anyone run into this before?
>
> [snip]
>
> Don't worry about the INVALID status.
> It does NOT mean that the trigger won't run.
>
> Oracle keeps track of which database objects a stored procedure (or
> trigger) refrences. When any of those objects is ALTERed, the stored
> proc referencing them needs to be re-compiled. Oracle defers this
> compilation until an attempt is made to execute the refering procedure.
> The INVALID status is simply Oracle's way of noting that the db server
> needs to re-compile the proc before executing it.
>
> <snip>

What you said is only partially true. When something is invalid, it does need to be recompiled(or possibly changed, depending on what was altered), and if you are using Oracle's tools there will MAY not be a problem because oracle will TRY to recompile it. If you are using 3rd party development tools like Powerbuilder, the application will crash with a ora-6505 error since the object is invalid. you have 2 options, 1. check to make sure there is nothing invalid after altering table, 2. develop a procedure that can be called in the 3rd party app that will check for invalid objects and recompile them prior to being called(yes this may cause locks).

hope that helps.
joe

--
Joseph S. Testa, Oracle Database Administrator, mailto:jtesta_at_scioto.net
(home)
Vice-President Ohio Oracle Users Group, try http://web.scioto.net/jtesta
That is also the place to find the FREE, "how do I build a second, third,
etc, instance, without using oracle's installer, database scripts.
ICQ UIN:  2832230(&oracle, home).