Oracle触发器ORA-04098:触发器无效且重新验证失败 [英] Oracle Trigger ORA-04098: trigger is invalid and failed re-validation

问题描述

我正在尝试在oracle 10g数据库中创建一个简单的触发器.此创建触发器的脚本运行干净.

I am trying to create a simple trigger in an oracle 10g database. This script to Create the trigger runs clean.

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;           
/

但是当我跑步时:

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:触发器"JMD.NEWALERT"无效且重新验证失败 (受影响的0行)

ORA-04098: trigger 'JMD.NEWALERT' is invalid and failed re-validation (0 rows affected)

我不知道是什么导致此错误.您知道导致此错误的原因吗?还是为什么会这样?

I don't understand whats causes this error. Do you know what causes this error? Or why this is happening?

提前谢谢！

-大卫

推荐答案

Oracle将尝试重新编译引用的无效对象.这里的触发器是无效的，并且每次尝试插入一行时，它将尝试重新编译触发器，然后失败，从而导致ORA-04098错误.

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.

您可以select * from user_errors where type = 'TRIGGER' and name = 'NEWALERT'查看触发器实际发生的错误以及为什么不进行编译.在这种情况下，您似乎在insert行的末尾缺少分号:

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 insert line:

INSERT INTO Users (userID, firstName, lastName, password)
VALUES ('how', 'im', 'testing', 'this trigger')

所以做到:

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;           
/

如果在执行操作时收到编译警告，则可以使用show errors(如果您使用的是SQL * Plus或SQL Developer)，或者再次查询user_errors.

If you get a compilation warning when you do that you can do show errors if you're in SQL*Plus or SQL Developer, or query user_errors again.

当然，这假设您的Users表确实具有这些列名，并且它们全都是varchar2 ...但是想必您实际上会对触发器做一些更有趣的事情.

Of course, this assumes your Users tables does have those column names, and they are all varchar2... but presumably you'll be doing something more interesting with the trigger really.

这篇关于Oracle触发器ORA-04098:触发器无效且重新验证失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！