1

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)

  • Please add your table schema. – McNets Apr 27 '17 at 12:40
  • Please add your table schema. – McNets - done. – Yegor Trembovetsky Apr 27 '17 at 12:48
  • order_item does not have a id_product column, for a start – Philᵀᴹ Apr 27 '17 at 13:02
  • sorry, the original code contains id_watch instead of id_product, I'll fix it – Yegor Trembovetsky Apr 27 '17 at 13:05
0

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;
  • Thanks, that works correctly, but that "Enter binds" window still appears, it seems to me that it caused by sql developer, not syntax. – Yegor Trembovetsky Apr 27 '17 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.