CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Meythaler.A
Meythaler.A

Reputation: 9

Creating a trigger that updates a table using data from two different tables

OK, so I'm now trying to create a trigger that helps to update a summary table that contains a product id, total sales, and total quantity of items per product. Essentially, I need to create a trigger that fires after the update of an order, when the orderplaced column is set to a value of '1' the summary table will need to be updated by a fired trigger grabbing the data from two other tables which are basket and basketitem to reference the idproduct. I have created the code but for more that i think of it and analyze it, i can't get to an valid compiled trigger that works. I will be adding my code so you can have an idea of what I'm trying to do here. thanks! 

    create or replace
    TRIGGER BB_SALESUM_TRG
    AFTER UPDATE OF orderplaced ON BB_BASKET 
    DECLARE
     CURSOR salesum_cur IS
          SELECT bi.idproduct as idp, sum(b.total) as tot, sum(b.quantity) as qua, 
    bi.orderplaced as orpl
          FROM bb_basket b, bb_basketitem bi
          WHERE b.idbasket = bi.idbasket;
    BEGIN
      FOR rec_cur IN salesum_cur LOOP
        IF rec_cur.orpl = 1 THEN
          INSERT INTO bb_sales_sum (idproduct, tot_sales, tot_qty)
          VALUES (rec_cur.idp, rec_cur.tot, rec_cur.qua));
        END IF;
      END LOOP;  
    END;

I have tried it in different ways, this is the last one I have though. I was also trying with using local variables instead of a cursor but neither way worked, any suggestions are very welcome !

thanks !

Upvotes: 0

Views: 2184

Answers (1)

ron tornambe
ron tornambe

Reputation: 10780

If I understand your requirements correctly, the following PL/SQL should point you in the right direction. I was unable to test this:

CREATE OR REPLACE TRIGGER BB_SALESUM_TRG
AFTER UPDATE OF orderplaced ON BB_BASKET
FOR EACH ROW
WHEN (new.orderplaced = 1)
BEGIN
  INSERT INTO bb_sales_sum(idproduct, tot_sales, tot_qty number)
  SELECT idproduct, sum(b.total), sum(b.quantity)
  FROM bb_basket b INNER JOIN bb_basketitem bi
  ON b.idbasket = bi.idbasket 
  WHERE b.idbasket = :new.idbasket
  GROUP BY idproduct
END;

Upvotes: 0

Related Questions