column not allowed here

CREATE OR REPLACE TRIGGER update_QOH
BEFORE INSERT ON ORDERLINE
FOR EACH ROW
DECLARE
    QOH_PRODUCT PRODUCT.QOH%TYPE;
    ORD_开发者_高级运维NO ORDERS.ORDER_NO%TYPE;
BEGIN
    SELECT QOH INTO QOH_PRODUCT FROM PRODUCT
    WHERE :old.product_no = :new.product_no;

    SELECT ORDER_NO INTO ORD_NO FROM ORDERLINE
    WHERE :old.order_no = :new.order_no;

    IF (:new.QTY <= QOH_PRODUCT) THEN   
        UPDATE PRODUCT SET QOH = QOH_PRODUCT - :new.QTY;
    ELSE
        send_email(ord_no, 'Backorder');

        INSERT INTO BACKORDER
        VALUES (backorder_no_seq.NEXTVAL, :new.product_no, :new.qty, SYSDATE);

        INSERT INTO PRODVENDOR
        VALUES (po_no_seq.NEXTVAL, vendor_no, :new.product_no, vend_qty,
            shipping_method, SYSDATE, NULL, NULL, NULL);
    END IF; 
END;
/
-------------------------------------------------------------------------------
Error(13,3): PL/SQL: SQL Statement ignored

Error(13,91): PL/SQL: ORA-00984: column not allowed here
--------------------------------------------------------------------------------

Product table (P_no, QOH, etc)

Orderline table (OL_no, QTY, etc)

Backorder table (B_no, B_QTY, etc)

Vendor table (V_no, etc)

I need to make sure that when a customer buys a product, there is enough QOH in product table, if there is, QOH in Product should be decreased (updated). If not, send email to customer, update the backorder table and the product should be ordered from the vendor.

---

Change this line:

QOH = :old.QOH - :new.QTY

to

QOH := :old.QOH - :new.QTY

In PL/SQL := is the assignment operator, so use it when you are setting a PL/SQL variable.

Whatever the source of your compile errors, sending an email from a trigger seems like a bad idea.

---

Firstly, consider putting the insert into a procedure instead of a trigger. (And a more helpful name, maybe). Inserting to or updating other tables from here could get messy if they get their own triggers later, and it's hard to keep track of what's going on where and when.

Secondly, this looks like it won't handle concurrent inserts very well - two new ORDERLINE inserts for the same product at the same time will try to update the product QOH with probably unexpected, or undesirable, results - QOH can go negative, for example. You're also potentially going to get multiple orders to the vendor; every order line that requests an out-of-stock product is going to make a new order to the vendor, even if each order line quantity is 1 and you order 100 at a time from the vendor.

Thirdly, various code errors; I'll start with a couple of more obvious ones:

a) You're selecting from PRODUCT and ORDERLINE with WHERE :old.product_no = :new.product_no. I'm not sure :OLD is even set in a before-insert trigger, but if it is it will be the same as :NEW or null, so you're possibly going to get ORA-02112 or ORA-01403 errors as it'll find all rows, or maybe none.

b) Your select from ORDERLINE will return no rows on the first order line and multiple rows from the third onwards anyway, so again you'll get ORA-01403 and ORA-02112 errors. But it's pointless as you're just selecting the value you're querying on. You can just use the :NEW value in the email call.

c) Your update of PRODUCT has no WHERE clause, so all QOH values will be updated.

CREATE OR REPLACE TRIGGER update_QOH
BEFORE INSERT ON ORDERLINE
FOR EACH ROW
DECLARE
    QOH_PRODUCT PRODUCT.QOH%TYPE;
BEGIN
    SELECT QOH INTO QOH_PRODUCT FROM PRODUCT
    WHERE product_no = :new.product_no;

    IF (:new.QTY <= QOH_PRODUCT) THEN   
        UPDATE PRODUCT SET QOH = QOH_PRODUCT - :new.QTY;
        WHERE product_no = :new.product_no;
    ELSE
        send_email(:new.order_no, 'Backorder');

        INSERT INTO BACKORDER
        VALUES (backorder_no_seq.NEXTVAL, :new.product_no, :new.qty, SYSDATE);

        INSERT INTO PRODVENDOR
        VALUES (po_no_seq.NEXTVAL, vendor_no, :new.product_no, vend_qty,
            shipping_method, SYSDATE, NULL, NULL, NULL);
    END IF; 
END;
/

d) Where are vend_no, vend_qty and shipping_method coming from in the insert to PRODVENDOR? This is the only obvious compilation error that jumps out.

e) You aren't specifying the table columns in the inserts. This will cause a compilation error if you have the values in the wrong order, or any missing, but you can't tell just by looking at the code. (You haven't said what the 'so may' errors are after @WW's change, so don't know if this is relevant). And if another column is added later, this trigger will become invalid, so it's generally a good idea to list the columns explicitly.

Functionally you seem to be sending an email saying the whole order is on back-order, rather than just this product; and there don't seem to be any links between the current order and what you're putting in to BACKORDER and PRODVENDOR.

---

Are you sure that QOH is a field in the ORDERLINE table? The :OLD. and :NEW. keywords are applicable only to the table on which the trigger is installed.