Trigger to update another table

CREATE TRIGGER update_orderline

AFTER INSERT OR UPDATE ON ORDERS

FOR EACH ROW

BEGIN

  INSERT INTO ORDERLINE(orderline_no, qty, order_no, product_no)

  VALUES (ol_no.nextval, :new.qty, :new.order_no, :new.product_no);

END;

I am trying to create a trigger that updates orderline table after a new record has been inserted into orders. But I get this error:

Error(3,26): PLS-00049: bad bind variable 'NEW.QTY'

Error(3,51): PLS-00049: bad bind variable 'N开发者_开发技巧EW.PRODUCT_NO'

If I had to guess the orders table does not have columns named 'order_no' and 'product_no'. I can reproduce the error in that case and get the bind variable message. So what is the orders table column names?

UPDATE: there is nothing to get the new value from for order_no or product_no or for that matter qty according to your comment. Where do you expect the values to come from?

UPDATE: Accordin to your update you have the following columns in the orders table:

order_no,
employee_no,
branch_no,
order_date,
ship_date,
shipping_method,
tax_status,
subtotal,
tax_amt,
shipping_charge,
total_amt,
customer_no

In that list of columns I do not see product_no or qty. Order_no is there though.

I think I found.

Look here : http://www.tek-tips.com/viewthread.cfm?qid=1556226&page=14

You have inadvertently discovered why, in the "Oracle World" it is generally bad form to code user-defined names within double quotes. You see, whenever you define a name in Oracle using double quotes and any alpha character that is not UPPER CASE, then you must always use double quotes and the same mixed-case configuration. If you do not use double-quotes, then Oracle presumes that regardless of your case in the code, that your Oracle name is uppercase !

So, when your referred, in your code, to "...INTO :new.user_idx...", Oracle looks for "USER_IDX", which it cannot find, since you defined that column as *"user_idx"* -- "user_idx" <> "USER_IDX".

If you sanitize your code of all double quotes, then your problem(s) should disappear.