Triggers in Oracle PL/SQL Problems

Question

I have just written a stored procedure and stored function that serve to insert a new row into my Orders table. The row update inserts: Ordernum, OrderDate, Customer, Rep, Manufacturer, Product, Qty, and SaleAmount.

I now have to write a trigger that updates my Salesreps table by adding the amount of the order just added. I am unsure of how to reference the rows. I have tried this:

 CREATE OR REPLACE TRIGGER UpdateSalesrep
   AFTER INSERT ON Orders
   FOR EACH ROW
   BEGIN 
      UPDATE Salesreps
      SET Sales = Sales + :NEW.Amount
      WHERE Rep = Salesrep;
End;
/

Sales is the name of the column on the Salesrep table. Amount if the name used in the stored proc. I am getting an error returned on 'Where Rep = Salesrep'. If I don't include this line, the trigger does not return any errors. However, I am assuming that if I can't figure out how to tie the sales amount into the one salesrep that made the sale, I will update every salesrep (which I'm sure they would be quite happy with). Any help would be greatly appreciated, as always.

Sorry, I try to explain everything as concisely as I can, but miss something. Salesrep is the name of the row in the Salesreps table (which is referenced by the Rep column in the Orders table, which is what the stored procedure inserts the row into). — unit
why didn't you put NEW: before Rep then? (see in answer) — zerkms

zerkms zerkms · Accepted Answer · 2011-04-05T02:04:13

 CREATE OR REPLACE TRIGGER UpdateSalesrep
   AFTER INSERT ON Orders
   FOR EACH ROW
   BEGIN 
      UPDATE Salesreps
      SET Sales = Sales + :NEW.Amount
      WHERE Salesrep = :NEW.Rep;
End;
/

Triggers in Oracle PL/SQL Problems

2 Answers