Compound Trigger im Falle von Mutating Table

Heute geht's um die Verwendung von Compund Triggern. Ein Compound Trigger fasst die verschiedenen Ereignisse (BEFORE STATEMENT, BEFORE EACH ROW...) in einem Block zusammen. Zudem enthält ein Compound Trigger einen Abschnitt zur Deklaration von Variablen der dann von allen Ereignissen verwendet werden kann. Genau dieser Abschnitt eigent sich dann für die Behandlung von Mutating Table Problemen. Zur Demonstration soll in der Tabelle EMP sichergestellt werden, dass die Summe der Gehält ein Budget der jeweiligen Abteilung nicht überschreitet.

Zunächst wird dazu die Tabelle DEPT um die Spalte SALBUDGET ergänzt:

alter table dept add salbudget integer;

Dieser Post verwendet die folgenden Werte für die Spalte SALBUDGET:

select
 deptno, salbudget
from
 dept;

DEPTNO         SALBUDGET     
-------------- --------------
10             13000         
20             15000         
30             11000         
40             10000

Der Compound Trigger soll nun sicherstellen, dass das Budget der Abteilung nicht überschritten wird. Dazu werden die betroffenen Abteilungen im BEFORE EACH ROW Teil in einer Nested Table gespeichert, um diese anschließend im AFTER STATEMENT Teil zu überprüfen.

create trigger check_salbudget_c_iu for insert or update of sal, deptno on emp

compound trigger

 type dept_nt is table of dept.deptno%type;
 v_dept_tab dept_nt := dept_nt();
 
 before each row is
 begin
  if 
  (
   inserting or (updating and (:new.sal > :old.sal or :new.deptno != :old.deptno))
  ) 
  then
   v_dept_tab.extend;
   v_dept_tab(v_dept_tab.last) := :new.deptno;
  end if;
 end before each row;
 
 after statement is
  v_difference integer;
 begin
  v_dept_tab := set(v_dept_tab);
  for i in 1 .. v_dept_tab.count loop
   select
    (select sum(sal) from emp where deptno = v_dept_tab(i)) 
     -
    (select salbudget from dept where deptno = v_dept_tab(i))
   into
    v_difference
   from
    dual;
   if (v_difference > 0) then
    raise_application_error
    (
     -20010,
     'Budget exceeded for department ' || v_dept_tab(i) || '.'
    ); 
   end if;
  end loop;
 end after statement;
 
end check_salbudget_c_iu;

Anmerkung: Die Funktion SET in Zeile 23 stellt sicher, dass eine Abteilung nicht mehrfach überprüft werden muss indem doppelte Einträge aus der Nested Table entfernt werden. Hinzu kommt, dass ein UPDATE nur dann zu einer Überschreitung des Budgets führen kann, wenn das Gehalt erhöht wird order sich die Abteilung ändert; dies wird in Zeile 12 überpüft.

Nun sollen zum Test die Gehälter aller Personen in Abteilung 30 um 20% erhöht werden:

update 
 emp
set 
 sal = sal * 1.20
where 
 deptno = 30;

Die Summe der Gehälter in Abteilung 30 beträgt nach dieser Veränderung 11040; somit eine Überschreitung des Budgets von 11000. Man erhält die entsprechende Fehlermeldung:

SQL-Fehler: ORA-20010: Budget exceeded for department 30.
ORA-06512: in "EXAMPLE.CHECK_SALBUDGET_C_U", Zeile 26
ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_SALBUDGET_C_U

Ein Compound Trigger bietet also im Wesentlichen die Funktionalität, die man vorher (vor Oracle 11g) mithilfe von Package Variablen implementieren musste.