Incorrect :NEW syntax

oracleplsqltrigger

I work in Oracle SQL Developer and use Oracle 11g express edition.

Tables, that are invoked here look like:

CREATE TABLE orders
(
    id_order             INTEGER NOT NULL ,
    total                NUMBER(10,2) NOT NULL ,
    id_user              NUMBER NOT NULL ,
    purchase_date        DATE NOT NULL ,
    id_delivery          NUMBER NOT NULL ,
    id_payment           NUMBER NOT NULL ,
    id_status            NUMBER NOT NULL ,
    distance             NUMBER(5) NULL 
);    

CREATE TABLE order_item
(
    id_item              NUMBER NOT NULL ,
    id_order             NUMBER NOT NULL ,
    id_watch             NUMBER NOT NULL ,
    quantity             NUMBER NOT NULL 
);

CREATE TABLE users
(
    id_user              NUMBER NOT NULL ,
    login                VARCHAR2(100) UNIQUE NOT NULL ,
    pass                 VARCHAR2(100) NOT NULL ,
    email                VARCHAR2(200) NOT NULL ,
    full_name            VARCHAR2(200),
    birth_date           DATE ,
    balance              NUMBER(15,6),
    id_role              NUMBER NOT NULL ,
    discount             NUMBER
);

CREATE TABLE watches
(
    id_watch             NUMBER NOT NULL ,
    id_brand             NUMBER NOT NULL ,
    id_mechanism         NUMBER NOT NULL ,
    id_bracelet          NUMBER NOT NULL ,
    id_gender            NUMBER NOT NULL ,
    cost                 NUMBER(8,2) NOT NULL ,
    model                VARCHAR2(100) NOT NULL ,
    description          VARCHAR2(2000) NOT NULL ,
    image_path           VARCHAR2(100) NOT NULL ,
    id_factory_country   NUMBER NOT NULL ,
    id_stock             NUMBER NULL
);

When I try to compile next code:

CREATE OR REPLACE TRIGGER addItemInOrder AFTER INSERT ON order_item
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;

I get a message box like this:
enter image description here

This seems that :NEW is treated as bindable variable, but when I omit semicolon I get another error:

Error(4,25): PL/SQL: ORA-00904: "NEW"."ID_ORDER": invalid identifier

That is sad as well.

So I have 2 questions:

1) Is it correct to use :NEW in AFTER INSERT trigger in WHERE clause?

2) Why everything compiles and works fine when I execute SET DEFINE OFF; before DDL statement?

And of course, thanks for your answers)

Best Answer

You were nearly there with your code. Fixed trigger below:

CREATE OR REPLACE TRIGGER add_item_in_order AFTER INSERT ON order_item
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total + (:NEW.quantity * (SELECT cost FROM watches WHERE id_watch = :NEW.id_watch))
WHERE orders.id_order = :NEW.id_order;
END;
Related Question