本文介绍了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触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!