PL/SQL update trigger errors

Question

create or replace TRIGGER PRODUCT_REORDER_AU
AFTER UPDATE ON PRODUCT
FOR EACH ROW

BEGIN

IF (PRODUCT.QUANTITY_IN_STOCK < PRODUCT.REORDER_POINT)

THEN

INSERT INTO PURCHASE_ORDER
(PO_NO, PO_DATE, PRODUCT_ID, QUANTITY, SUPPLIER_ID)
VALUES
(OLD.PO_NO, NEW.PO_DATE, OLD.PRODUCT.PRODUCT_ID, OLD.PRODUCT.REORDER_QUANTITY, OLD.PRODUCT.SUPPLIER_ID);

END IF;
END;

I'm trying to create a trigger to use on an update operation but am getting the following errors:

Error(8,1): PL/SQL: Statement ignored
Error(8,13): PLS-00357: Table,View Or Sequence reference 'PRODUCT.QUANTITY_IN_STOCK' not allowed in this context

I'm not sure I'd be inserting purchase orders automatically - into a review queue certainly, or a pending queue of some sort. Purchasers may want a bit more control over some of this, or for doing things like combined orders. Also, you probably want to include the count of products on order in this too, or you could end up with more of a product than you want (ie - reorder point for candy bars is 20, and you purchase them in boxes of 100. But customers purchase them a bar at a time...) — Clockwork-Muse
– Clockwork-Muse, Commented Apr 2, 2014 at 21:38
I agree, but for the purposes of this assignment I need to be able to create automatic entries. Thanks for the input though :) — user3385472
– user3385472, Commented Apr 2, 2014 at 21:39

Justin Cave · Accepted Answer · 2014-04-02 22:19:36Z

2

My guess is that you want to check the :new.quantity_in_stock and :new.reorder_point values. And remember that the :new and :old pseudo-records are prefixed with colons.

IF (:new.QUANTITY_IN_STOCK < :new.REORDER_POINT)
THEN
  INSERT INTO PURCHASE_ORDER
    (PO_NO, PO_DATE, PRODUCT_ID, QUANTITY, SUPPLIER_ID)
    VALUES
    (:OLD.PO_NO, :NEW.PO_DATE, :OLD.PRODUCT_ID, 
     :OLD.REORDER_QUANTITY, :OLD.SUPPLIER_ID);
END IF;

edited Apr 2, 2014 at 22:19

answered Apr 2, 2014 at 21:05

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3385472 Over a year ago

For the values being inserted, it's throwing an error "bad bind variable"

Justin Cave Over a year ago

@user3385472 - I didn't notice that you had old.product.column_name rather than :old.column_name. Updated my answer.

evenro · Accepted Answer · 2014-04-02 21:17:34Z

1

Two things -

Don't forget the ":" before old and new.
you don't need to write the table name.
:old.product.product_id can be changed to :old.product_id

answered Apr 2, 2014 at 21:17

evenro

2,66422 silver badges35 bronze badges

Collectives™ on Stack Overflow

PL/SQL update trigger errors

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related