azeraz

CREATE OR REPLACE TRIGGER P6_1_ORD 
before INSERT OR UPDATE ON s_ord 
FOR each ROW DECLARE TYPE TYPE_COLL_PERS IS TABLE OF S_EMP%ROWTYPE;
t_pers TYPE_COLL_PERS;
v_credit_rating s_customer.credit_rating%TYPE;
BEGIN
  SELECT * BULK COLLECT
    INTO t_pers
    FROM s_emp
    WHERE id = :NEW.sales_rep_id;
    SELECT credit_rating
    INTO v_credit_rating
    FROM s_customer
    WHERE id =:NEW.customer_id;
 
    IF (t_pers.COUNT = 0 OR UPPER(t_pers(1).title) <> 'SALES REPRESENTATIVE') THEN
      RAISE_APPLICATION_ERROR(-20000,'ORDERMAKER MOET SALES REPRESENTATIVE ZIJN');
    END IF;
 
    IF ((v_credit_rating <> 'GOOD' OR v_credit_rating <> 'EXCELLENT') AND :NEW.payment_type = 'CASH') THEN
      RAISE_APPLICATION_ERROR(-20000,'Klant kan alleen met cash betalen als zijn krediet goed of excellent is');
    END IF;
END P6_1_ORD ;