Oracle 中触发器无效
在对表进行某些更改后，我的数据库中的某些触发器变得无效。但他们似乎仍在工作。我遇到的唯一问题是，如果我使用 SQL Developer，触发器的左侧会出现红叉，表明它们无效。这是一个大问题吗？
我知道我可以重新编译触发器来解决这个问题，但我不确定这是否真的是一个值得关注的问题。如果是这样，我将需要检查之前的数百个更改并找出导致问题的原因。谢谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
每当我们将更改部署到数据库对象时，依赖于它的任何代码都会失效。这会影响触发器、视图和存储过程。但是，下次调用该代码时，数据库将自动重新编译它。
所以我们不需要担心这个，对吧？嗯，是的，在某种程度上。问题是，触发器（或其他）的失效对我们来说是一个标志，表明已经进行了更改，这可能会影响该触发器的操作，这可能会产生副作用。最明显的副作用是触发器无法编译。更巧妙的是，触发器可以编译，但在操作期间会失败。
因此，最好在开发环境中强制重新编译触发器，以确保我们的更改不会从根本上破坏任何内容。但是，当我们在生产中部署更改时，我们可以跳过这一步，因为我们确信所有内容都会根据需要重新编译。取决于我们的勇气:)
Oracle 提供了自动重新编译模式中所有无效对象的机制。
最简单的方法是使用
DBMS_UTILITY.COMPILE_SCHEMA()。但自 8i 以来，这一直很危险（因为对 Java 存储过程的支持引入了循环依赖的可能性），并且不再保证一次性成功编译所有对象。在 9i 中，Oracle 为我们提供了一个脚本
$ORACLE_HOME/rdbms/admin/utlrp.sql来重新编译内容。不幸的是它需要 SYSDBA 访问权限。在 10g 中，他们添加了 UTL_RECOMP 包，它基本上完成了该脚本所做的所有事情。这是重新编译大量对象的推荐方法。不幸的是，它还需要 SYSDBA 访问权限。 了解更多。
在 11g 中，Oracle 引入了细粒度的依赖管理。这意味着对表的更改会以更细的粒度（基本上是列级别而不是表级别）进行评估，并且只有直接受更改影响的对象才会受到影响。
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.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.
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.
根本不是什么大问题。
只需右键单击它们即可重新编译，然后就可以开始了...我是根据自己的经验写这篇文章的。
如果您刚刚更改的代码有任何错误，它们将会出现，以便您可以修复它。如果出现错误，编译器会告诉您问题出在哪里（行号、变量名等）。
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.
如果触发器正常工作，那么 Oracle 在触发触发器并在自动重新编译后重试触发器时可能会捕获 ORA-04068 错误。
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.