trigger help

Hello,
 I have three tables in this DB I'm building that will handle materials, ordering, and receiving. I admit to being a bit inexperienced writing triggers, but here's what I'm trying to accomplish.

 orders_master has the order information (order_ID, date ordered date, received, vendor, etc.
orders_detail contains the line items related to that order (item, cost, quantity ordered). It is joined to orders_master on the column order_ID. materials contains information about the items in stock and has a quantity onhand. It is joined to orders_detail on the column material_ID.

I have made many attempts to write a trigger which will add the value of orders_detail.qty_ordered to materials.qty_onhand in materials (joined on order_ID), but only when the previous value of orders_master.date_received was null (to prevent the quantity from updating when you simply change the received_date on a previously received order).

Here's what I have, with the error messages:

SQL> create or replace trigger receive
  2 after insert or update on orders_master   3 for each row
  4 when (old.received_date IS NULL
  5 and new.received_date IS NOT NULL)   6 begin
  7 update materials set qty_onhand =   8 (qty_onhand + orders_detail.qty_ordered)   9 where :new.order_ID = orders_detail.order_ID  10 and materials.material_ID = orders_detail.material_ID;  11 end receive;
 12 /

Warning: Trigger created with compilation errors.

SQL> sho errors
Errors for TRIGGER RECEIVE:

LINE/COL ERROR

-------- ----------------------------------------------------
2/3      PL/SQL: SQL Statement ignored
5/45     PL/SQL: ORA-00904: invalid column name

Besides not being able to get it to work, I'm also a bit confused on the second error, since row 5 doesn't extend to 45 characters.

If anyone has any guidance, I'd be grateful.

Thanks,
Mike Trozzo Received on Wed May 08 2002 - 10:22:57 CDT