Mikrozadanie 6

backback

Treść

Dodaj do tabeli emp wyzwalacz, który nie pozwoli spowodować, że któryś departament będzie pusty albo większy niż dziesięcioosobowy.

Wzorcówka

CREATE OR REPLACE TRIGGER TRG_after_any_emp_deptno
AFTER INSERT OR DELETE OR UPDATE OF DEPTNO ON emp
DECLARE 
    max_count INTEGER;
    min_count INTEGER;
BEGIN 
    SELECT MIN(COUNT(e.empno)) INTO min_count
      FROM dept d
      LEFT JOIN emp e
        ON d.deptno = e.deptno
    GROUP BY d.deptno;
     
    SELECT MAX(COUNT(e.empno)) INTO max_count
      FROM dept d
      LEFT JOIN emp e
        ON d.deptno = e.deptno
    GROUP BY d.deptno;
    
    IF min_count < 1 THEN
        raise_application_error(-20000, 'Operation would cause a department to have ' || min_count || ' employees, while the minimum is 1.');
    END IF;
      IF max_count > 10 THEN 
        raise_application_error(-20000, 'Operation would cause a department to have ' || max_count || ' employees, while the maximum is 10.');
    END IF;
END;

Uwagi

  1. Tak jak ustaliliśmy (patrz Lab 6), na Oraclu triggery FOR EACH ROW odnoszące się do tabeli emp nie działają, ponieważ nie mamy gwarancji, że cała operacja wykonała się przed odpaleniem triggera, więc możemy nie widzieć pełnych danych. Rozwiązaniem jest użycie sensowniejszej bazy niż Oracle, albo zrezygnowanie z FOR EACH ROW.
  2. Kilka osób robiło ten check proceduralnie:
FOR dept_row IN (SELECT * FROM dept d) LOOP
  SELECT COUNT(*) INTO deptno_count FROM emp e WHERE e.deptno = dept_row.deptno;
  ... -- check
END LOOP;

O ile w tym przypadku nie ma to większego znaczenia, to proszę pamiętać, że jeśli departamentów jest dużo to taki trigger będzie działać nieporównywalnie wolniej od takiego, który wykonuje jedno zapytanie na całej tabeli dept. Bazy danych są zoptymalizowane pod szybkie zapytania i joiny, a nie operacje proceduralne.

backback