触发器在 Oracle 中无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3200202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger is invalid in Oracle
提问by newguy
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?
在对表进行某些更改后，我的数据库中的某些触发器无效。但似乎他们仍在工作。我遇到的唯一问题是，如果我使用 SQL Developer，触发器左侧会出现红色叉号，表明它们是无效的。这是一个大问题吗？
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.
我知道我可以重新编译触发器来解决这个问题，但我不确定这是否真的是一个值得关注的问题。如果是这样，我将需要查看我之前的数百个更改并找出导致问题的原因。谢谢你。
回答by APC
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.
Oracle 提供了自动重新编译模式中所有无效对象的机制。
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.sqlwhich 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.
最直接的就是使用
DBMS_UTILITY.COMPILE_SCHEMA(). 但这从 8i 开始就很棘手（因为对 Java 存储过程的支持引入了循环依赖的可能性）并且不再保证第一次成功编译所有对象。在 9i 中，Oracle 给了我们一个
$ORACLE_HOME/rdbms/admin/utlrp.sql重新编译东西的脚本 。不幸的是，它需要 SYSDBA 访问权限。在 10g 中，他们添加了 UTL_RECOMP 包，它基本上完成了该脚本所做的一切。这是重新编译大量对象的推荐方法。不幸的是，它还需要 SYSDBA 访问权限。 了解更多。
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.
在 11g 中，Oracle 引入了细粒度的依赖管理。这意味着对表的更改以更精细的粒度（基本上是列级别而不是表级别）进行评估，并且只有直接受更改影响的对象才会受到影响。 了解更多。
回答by Leniel Maccaferri
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.
如果您刚刚更改的代码有任何错误，它们就会出现，以便您可以修复它。如果出现错误，编译器会告诉您问题出在哪里（行号、变量名等）。
回答by DCookie
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.
如果触发器正常工作，则 Oracle 在触发触发器并在自动重新编译后重试触发器时可能会捕获 ORA-04068 错误。
