ORA-04076:无效的新旧规范-PL/SQL-Oracle触发器 [英] ORA-04076: invalid NEW or OLD specification - PL/SQL - Oracle Trigger

问题描述

我正在尝试编写此触发器:

I'm trying to write this trigger:

CREATE OR REPLACE TRIGGER ManageCall
AFTER INSERT 
ON STATE_CHANGE
FOR EACH ROW
WHEN (ChangeType='C')
DECLARE
x_coord NUMBER;
y_coord NUMBER;
BEGIN
    SELECT X, Y INTO x_coord, y_coord
    FROM TELEPHONE
    WHERE PHONENO = :NEW.PHONENO;
END;

引用这些表的

:

that refers to these tables:

TELEPHONE
PHONENO (CHAR) 
X (NUMBER)
Y (NUMBER)
PHONESTATE (CHAR)

STATE_CHANGE
CHANGEID (NUMBER)
TIMESTAMP (DATE)
PHONENO (CHAR)
X (NUMBER)
Y (NUMBER)
CHANGETYPE (CHAR)

我认为以正确的方式使用:NEW，但我收到此消息:

I think to have use :NEW in the right way but I obtain this message:

ORA-04076: invalid NEW or OLD specification

有人可以向我解释我在哪里做错了.

Can someone explain me where I do a mistake.

非常感谢.

推荐答案

每次引用插入的列时，都必须使用:NEW和:OLD.

You have to use :NEW and :OLD everytime you are refering to the columns that are inserted.

CREATE OR REPLACE TRIGGER ManageCall
    AFTER INSERT 
    ON STATE_CHANGE
    FOR EACH ROW
    DECLARE
    x_coord NUMBER;
    y_coord NUMBER;
    BEGIN
    IF :NEW.ChangeType='C' THEN
        SELECT X, Y INTO x_coord, y_coord
        FROM TELEPHONE t
        WHERE t.PHONENO = :NEW.PHONENO;
    END IF;
    END;

使用WHEN子句时，不能将NEW用作绑定变量，如

When you are using the WHEN-clause, you can't use NEW as a bind variable as stated here (search for 'WHEN clause'):

在WHEN子句中指定时，NEW和OLD关键字不被视为绑定变量，因此不能以冒号(:)开头.但是，在WHEN子句以外的所有引用中，必须在NEW和OLD之前加冒号.

The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause.

因此以下内容也应工作:

So the following should work as well:

CREATE OR REPLACE TRIGGER ManageCall
    AFTER INSERT 
    ON STATE_CHANGE
    FOR EACH ROW
    WHEN (NEW.ChangeType='C')
    DECLARE
    x_coord NUMBER;
    y_coord NUMBER;
    BEGIN
        SELECT X, Y INTO x_coord, y_coord
        FROM TELEPHONE
        WHERE PHONENO = :NEW.PHONENO;
    END;

这篇关于ORA-04076:无效的新旧规范-PL/SQL-Oracle触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！