SQL Oracle 触发器 ORA-04098：触发器无效且重新验证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22668507/
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
Oracle Trigger ORA-04098: trigger is invalid and failed re-validation
提问by user3412162
I am trying to create a simple trigger in an oracle 10g database. This script to Create the trigger runs clean.
我正在尝试在 oracle 10g 数据库中创建一个简单的触发器。这个创建触发器的脚本运行干净。
CREATE OR REPLACE TRIGGER newAlert
AFTER INSERT OR UPDATE ON Alerts
BEGIN
INSERT INTO Users (userID, firstName, lastName, password) VALUES ('how', 'im', 'testing', 'this trigger')
END;
/
But when I run:
但是当我运行时：
INSERT INTO Alerts(observationID, dateSent, message, dateViewed) VALUES (3, CURRENT_TIMESTAMP, 'Alert: You have exceeded the Max Threshold', NULL);
to activate the trigger, I get this error message:
要激活触发器，我收到此错误消息：
ORA-04098: trigger 'JMD.NEWALERT' is invalid and failed re-validation (0 rows affected)
ORA-04098: 触发器 'JMD.NEWALERT' 无效且重新验证失败（0 行受影响）
I don't understand whats causes this error. Do you know what causes this error? Or why this is happening?
我不明白是什么导致了这个错误。你知道是什么导致了这个错误吗？或者为什么会发生这种情况？
Thank you in advance!
先感谢您！
-David
-大卫
回答by Alex Poole
Oracle will try to recompile invalid objects as they are referred to. Here the trigger is invalid, and every time you try to insert a row it will try to recompile the trigger, and fail, which leads to the ORA-04098 error.
Oracle 将尝试重新编译引用的无效对象。这里触发器无效，每次尝试插入行时都会尝试重新编译触发器，失败，导致ORA-04098错误。
You can select * from user_errors where type = 'TRIGGER' and name = 'NEWALERT'to see what error(s) the trigger actually gets and why it won't compile. In this case it appears you're missing a semicolon at the end of the insertline:
您可以select * from user_errors where type = 'TRIGGER' and name = 'NEWALERT'查看触发器实际遇到的错误以及它无法编译的原因。在这种情况下，您似乎在行尾缺少分号insert：
INSERT INTO Users (userID, firstName, lastName, password)
VALUES ('how', 'im', 'testing', 'this trigger')
So make it:
所以让它：
CREATE OR REPLACE TRIGGER newAlert
AFTER INSERT OR UPDATE ON Alerts
BEGIN
INSERT INTO Users (userID, firstName, lastName, password)
VALUES ('how', 'im', 'testing', 'this trigger');
END;
/
If you get a compilation warning when you do that you can do show errorsif you're in SQL*Plus or SQL Developer, or query user_errorsagain.
如果在执行此操作时收到编译警告，show errors则可以在 SQL*Plus 或 SQL Developer 中执行此操作，或者user_errors再次查询。
Of course, this assumes your Userstables does have those column names, and they are all varchar2... but presumably you'll be doing something more interesting with the trigger really.
当然，这假设您的Users表确实有这些列名，而且它们都是varchar2……但据推测，您真的会对触发器做一些更有趣的事情。
回答by Kiran.Bakwad
Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
原因：尝试检索触发器以执行，但发现无效。这也意味着触发器的编译/授权失败。
Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.
操作：选项是解决编译/授权错误、禁用触发器或删除触发器。
Syntax
句法
ALTER TRIGGER trigger Name DISABLE;
ALTER TRIGGER trigger_Name ENABLE;
回答by Siva Anand
in my case, this error is raised due to sequence was not created..
在我的情况下，由于未创建序列而引发此错误..
CREATE SEQUENCE J.SOME_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
