本文介绍了ORA-04076:无效的新旧规范-PL/SQL-Oracle触发器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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子句"):

When you are using the WHEN-clause, you can't use NEW as a bind variable as stated here (search for '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触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 22:04
查看更多