mqiojfei

CREATE OR REPLACE
PACKAGE best_customer_pkg AS
 CURSOR c_totalen IS
 SELECT customer_id, SUM(total) AS totaal
  FROM S_ORD
  WHERE TO_CHARdDate_ordered,'mon') = TO_CHAR(SYSDATE,'mon')
  GROUP BY customer_id;
 TYPE type_tab_totalen IS TABLE OF c_totalen%ROWTYPE
 INDEX BY PLS_INTEGER;
 t_totalen type_tab_totalen;
END BEST_CUSTOMERS_PKG;
 
CREATE OR REPLACE TRIGGER vul_c_totalen
 BEFORE UPDATE OF order_filled ON s_ord 
BEGIN
  FOR r_totaal IN best_customers_pkg.c_totalen LOOP
    best_customers_pkg.t_totalen(best_customers_pkg.c_totalen%ROWCOUNT) := r_totaal;
 END LOOP;
END;
 
 
CREATE OR REPLACE
TRIGGER best_customers
 AFTER UPDATE OF order_filled ON s_ord 
FOR EACH ROW
WHEN (:NEW.order_filled = 'Y')
DECLARE
  v_customer_id NUMBER;
  v_totaal NUMBER;
  v_gevonden NUMBER;
BEGIN
  FOR tel IN 1..best_customers_pkg.t_totalen.COUNT 
  LOOP
    v_totaal := best_customers_pkg.t_totalen(tel).totaal;
    v_customer_id := best_customers_pkg.t_totalen(tel).customer_id;
    IF (v_customer_id = :NEW.customer_id AND v_totaal > 500) THEN
      SELECT 1 INTO v_gevonden
      FROM s_customer
      WHERE id IN (SELECT customer_id
                      FROM s_best_customer);
 
      IF (SQL%NOTFOUND) THEN
       INSERT INTO s_best_customer
       VALUES(:NEW.customer_id,TO_CHAR(SYSDATE,'mm-yyyy'));
      ELSE
       DBMS_OUTPUT.PUT_LINE('De klant staat reeds in de tabel s_best_Customers');
      END IF;
    END IF;
   END LOOP;
END;