sql - PL/SQL更新触发错误

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;

我正在尝试创建一个用于更新操作的触发器，但遇到以下错误:

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

Answer 1

我的猜测是您要检查:new.quantity_in_stock和:new.reorder_point值。请记住，:new和:old伪记录以冒号作为前缀。

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;