Posted By

wh0emPah on 04/02/10


Tagged


Versions (?)

mqiojfei


 / Published in: PL/SQL
 

  1. PACKAGE best_customer_pkg AS
  2. CURSOR c_totalen IS
  3. SELECT customer_id, SUM(total) AS totaal
  4. FROM S_ORD
  5. WHERE TO_CHARdDate_ordered,'mon') = TO_CHAR(SYSDATE,'mon')
  6. GROUP BY customer_id;
  7. TYPE type_tab_totalen IS TABLE OF c_totalen%ROWTYPE
  8. t_totalen type_tab_totalen;
  9. END BEST_CUSTOMERS_PKG;
  10.  
  11. CREATE OR REPLACE TRIGGER vul_c_totalen
  12. BEFORE UPDATE OF order_filled ON s_ord
  13. FOR r_totaal IN best_customers_pkg.c_totalen LOOP
  14. best_customers_pkg.t_totalen(best_customers_pkg.c_totalen%ROWCOUNT) := r_totaal;
  15.  
  16.  
  17. TRIGGER best_customers
  18. AFTER UPDATE OF order_filled ON s_ord
  19. FOR EACH ROW
  20. WHEN (:NEW.order_filled = 'Y')
  21. v_customer_id NUMBER;
  22. v_totaal NUMBER;
  23. v_gevonden NUMBER;
  24. FOR tel IN 1..best_customers_pkg.t_totalen.COUNT
  25. v_totaal := best_customers_pkg.t_totalen(tel).totaal;
  26. v_customer_id := best_customers_pkg.t_totalen(tel).customer_id;
  27. IF (v_customer_id = :NEW.customer_id AND v_totaal > 500) THEN
  28. SELECT 1 INTO v_gevonden
  29. FROM s_customer
  30. WHERE id IN (SELECT customer_id
  31. FROM s_best_customer);
  32.  
  33. IF (SQL%NOTFOUND) THEN
  34. INSERT INTO s_best_customer
  35. VALUES(:NEW.customer_id,TO_CHAR(SYSDATE,'mm-yyyy'));
  36. DBMS_OUTPUT.PUT_LINE('De klant staat reeds in de tabel s_best_Customers');

Report this snippet  

You need to login to post a comment.