CODEWITHSUNDEEP
oracle-databaseplsqltriggers
unit
unit

Reputation: 415

Triggers in Oracle PL/SQL Problems

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.

Upvotes: 2

Views: 495

Answers (2)

TTT
TTT

Reputation: 2375

Who have said that you have to write a trigger? Is this stated explicitly as a requirement in your homework task? You can also update table UpdateSalesRep in the same stored procedure that you have already written for inserting in table Orders.

Read: http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Upvotes: 1

zerkms
zerkms

Reputation: 255085

 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;
/

Upvotes: 4

Related Questions