oracle ORA-04079: 无效的触发器规范
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19639761/
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
ORA-04079: invalid trigger specification
提问by tomaytotomato
I am trying to create a trigger that audits two fields when they are updated in a table. I have tried compiling the PL/SQL, but the following error keeps coming up.
我正在尝试创建一个触发器，用于在表中更新两个字段时对其进行审核。我已经尝试编译 PL/SQL，但不断出现以下错误。
ORA-04079: invalid trigger specification
Referring to other SO questions they mention using Declareor Asto solve this problem
参考他们提到的其他 SO 问题，使用Declare或As来解决这个问题
CREATE OR REPLACE TRIGGER AUDIT_TRIGGER
BEFORE UPDATE OF MR_STATE, IS_EXEMPT
ON CX_MR mr
FOR EACH ROW
BEGIN
IF UPDATING ('MR_STATE') THEN
INSERT INTO CX_AUDIT
(
INT_ID,
FIELD_NAME,
OLD_VAL,
NEW_VAL,
CHANGED_DATE,
CHANGED_BY
)
VALUES
(
mr.INTEGRATION_ID,
'MR_STATE',
:old.MR_STATE,
:new.MR_STATE,
SYSDATE,
'Trigger'
);
END IF;
IF UPDATING ('IS_EXEMPT') THEN
INSERT INTO CX_AUDIT
(
INT_ID,
FIELD_NAME,
OLD_VAL,
NEW_VAL,
CHANGED_DATE,
CHANGED_BY
)
VALUES
(
mr.INT_ID,
'IS_EXEMPT',
:old.IS_EXEMPT,
:new.IS_EXEMPT,
SYSDATE,
'Multiroom'
);
END IF;
END;
However I am not sure what I should be declaring or if this is the solution.
但是，我不确定我应该声明什么，或者这是否是解决方案。
回答by Justin Cave
The error appears to be that you have an extra token "mr" after the table name CX_MR. You cannot alias a table name in a trigger definition.
错误似乎是您在表名之后有一个额外的标记“mr” CX_MR。您不能在触发器定义中为表名设置别名。
You then appear to go on to try to use the alias in the body of your trigger. Once you remove the "mr" token, the header of your trigger should compile but you'll get a compilation error when you are using mrin your INSERTstatements. I'm not sure that I understand what you are attempting to do there, though... You seem to be aware of how to use the :newand :oldpseudo-records to get the old and new values. My guess is that you just want the :new.int_idin both INSERTstatements. But it is not clear to me whether you are avoiding that construct for some reason and whether you expect mr.int_idto be something different.
然后，您似乎继续尝试在触发器主体中使用别名。删除“mr”标记后，触发器的标头应该会编译，但是mr在INSERT语句中使用时会出现编译错误。不过，我不确定我是否理解您在那里尝试做什么……您似乎知道如何使用:new和:old伪记录来获取旧值和新值。我的猜测是你只想要:new.int_id两个INSERT语句中的 。但我不清楚您是否出于某种原因避免了这种结构，以及您是否希望mr.int_id有所不同。
