One more question regarding triggers PL/SQL

I have run into a problem with a second trigger we are to write regarding the following:

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 Offices table by adding the amount of the newly added sale. Problem is, not every salesrep has an office assigned to them. I don't understand if I need to have a when clause under 'FOR EACH ROW' which somehow stipulates this, or if I need to stipulate after my SET Sales line. This is my code so far, but unfortunately, it updates all of the offices sales, not just the one that the salesrep belongs 开发者_开发知识库to:

CREATE OR REPLACE TRIGGER UpdateOffices
   AFTER INSERT ON Orders
   FOR EACH ROW
   BEGIN 
      UPDATE Offices
      SET Sales = Sales + :NEW.Amount
      WHERE Office IN (SELECT RepOffice
                       FROM Salesreps
                       WHERE RepOffice IS NOT NULL);
End;
/
SHOW ERRORS

Sales is the name of the column on the Offices table. Amount if the name used in the stored proc.

You've already been advised to add a predicate on Salesreps, e.g. WHERE Salesrep = :NEW.Rep. Why don't you add it to the subquery?

You are going to run into all sorts of problems implementing your solution using this trigger method.

The Office.Sales column value will become stale as soon as amendments to orders are committed to the database. For example, when the price or quantity on an order is changed or deleted.

I would recommend you implement this requirement as a 'Refresh on Commit' Materialized View.