Compound Triggers

 /*
 Oracle 11g offers a new twist on triggers, the compound trigger, a trigger that can act both before and after an update, insert or delete has occurred. This makes possible the ability in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.
 At least two of the sections must be included (including only one of the four would result in a traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be 'matched' (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:


 create or replace trigger <trigger name>
 for <insert|update|delete> <of column_name> on <tablename>
 COMPOUND TRIGGER
 <declare section>
 BEFORE
 <before section>
 BEFORE EACH ROW
 <before each row section>
 AFTER EACH ROW
 <after each row section>
 AFTER
 <after section>
 END;
 Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger, HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises
 */
 create or replace trigger check_raise_on_avg
 for update of sal on emp
 COMPOUND TRIGGER
 Twelve_Percent        constant number:=0.12;
 -- Declare collection type and variable:
 TYPE Department_Salaries_t  IS TABLE OF Emp.Sal%TYPE
 INDEX BY VARCHAR2(80);
 Department_Avg_Salaries     Department_Salaries_t;
 TYPE Sal_t IS TABLE OF Emp.Sal%TYPE;
 Avg_Salaries         Sal_t;
 TYPE Deptno_t IS TABLE OF Emp.Deptno%TYPE;
 Department_IDs              Deptno_t;
 BEFORE STATEMENT IS
 BEGIN
  SELECT AVG(e.Sal), NVL(e.Deptno, -1)
  BULK COLLECT INTO  Avg_Salaries, Department_IDs
  FROM Emp e
  GROUP BY e.Deptno;
  FOR j IN 1..Department_IDs.COUNT() LOOP
   Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
  END LOOP;
 END BEFORE STATEMENT;
  
 AFTER EACH ROW IS
 BEGIN
  IF :NEW.Sal - :Old.Sal >
     Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno) THEN
      Raise_Application_Error(-20000, 'Raise too large');
  END IF;
 END AFTER EACH ROW;
END Check_Raise_On_Avg;

select empno, sal from emp;

update emp set sal=sal*1.10 where empno = 7369;

select empno, sal from emp;

ROLLBACK;


select empno, sal,deptno from emp where deptno=20;

update emp set sal=sal*1.08 where deptno = 20;


select empno, sal,deptno from emp where deptno=20;

rollback;


-- Does Trigger Raise Exception
-- Now Watch
select empno, sal,deptno from emp where deptno=30;


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