What causes a trigger to become disabled?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 22 Nov 2002 10:36:03 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702F6366D@lnewton.leeds.lfs.co.uk>

Steve,

triggers can be imported as invalid, if something they depend upon - say another proc or fn - is not yet imported or is itself invalid.

Once the trigger is 'fired' for the first time, there will be a delay while it is compiled and if that works, the trigger will run as normal. All subsequent firings of the trigger will not need to recompile.

Now, say the trigger needs access to one or more of the V$ views, for example, and the database it has been imported into doesn't have the correct privs set to alow it access, then it will be invalid on import and will fail to recompile, and will die horribly with an error like 'trigger is in an invalid state and cannot be validated' - or similar.

If the system has run, and the trigger has been fired since the import (last week) then someone or something has been playing around with one or more objects upon which the trigger is dependant. They may have changed privs, altered a table etc etc.

There is a 'sort of' FAQ on the subject, with demonstrations, in the co-operative FAQ at http://www.jlcomp.demon.co.uk/faq/resetseq.html - which is about resetting a sequence without dropping it. The demo shows that dropping and re-creating a sequence causes triggers to go invalid.

Even though the trigger shows up as invalid, it will still be enabled (see demo) because you have not explicitly disabled it. The same invalid/valid problems occus on procs, functions pachaed or otherwise, when things 'change' on dependencies. But Oracle will attampt to compile these objects on first call, only if the compile fails will the attempted operation crap out - which sounds like what its happening to you.

If your system is failing because triggers are invalid, then the triggers have failed to recompile - you need to do a manual recompile and check the errors.

HTH Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: smontgomerie_at_hotmail.com (Steve) [mailto:smontgomerie_at_hotmail.com] Posted At: Thursday, November 21, 2002 6:42 PM Posted To: server
Conversation: What causes a trigger to become disabled? Subject: What causes a trigger to become disabled?

Hello,
How is it possible for a trigger to appear as disabled (in user_triggers) without explicity issuing ALTER TRIGGER ..DISABLE?

We exported the db to a client site, checked for invalid objects (there were none) and now a week later mysteriously proceses have stopped working which we have found is related to triggers being disabled.

Here's what I checked.
>Ran a simple testcase with a trigger that inserts values into
specific columns of a table. Dropped one of the columns that the trigger inserts into. The trigger shows as invalid but is still enabled
>Droped a table that the trigger inserts into. The trigger shows as
invalid but is still enabled.

I also thought that perhaps the trigger may not have got enabled on import however these processes have been working fine since the import until just the past day. The import occured over a week ago.

We are 90140 on win 2k

tks for any suggestion Received on Fri Nov 22 2002 - 04:36:03 CST