Re: Trigger problem

you can't use :new and :old in when clauses - only table columns.

"Mike Trozzo" <lock_and_load62_at_hotmail.com> wrote in message news:<cA1C8.7759$EY1.226_at_nwrddc02.gnilink.net>...
> 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 - 17:50:30 CEST