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.
Compound triggers can have up to four sections:
- the BEFORE section
- the BEFORE EACH ROW section
- the AFTER EACH ROW section
- the AFTER section
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:
SQL> create or replace trigger check_raise_on_avg
2 for update of sal on emp
3 COMPOUND TRIGGER
4 Twelve_Percent constant number:=0.12;
5
6 -- Declare collection type and variable:
7
8 TYPE Department_Salaries_t IS TABLE OF Emp.Sal%TYPE
9 INDEX BY VARCHAR2(80);
10 Department_Avg_Salaries Department_Salaries_t;
11 TYPE Sal_t IS TABLE OF Emp.Sal%TYPE;
12 Avg_Salaries Sal_t;
13 TYPE Deptno_t IS TABLE OF Emp.Deptno%TYPE;
14 Department_IDs Deptno_t;
15
16 BEFORE STATEMENT IS
17 BEGIN
18 SELECT AVG(e.Sal), NVL(e.Deptno, -1)
19 BULK COLLECT INTO Avg_Salaries, Department_IDs
20 FROM Emp e
21 GROUP BY e.Deptno;
22 FOR j IN 1..Department_IDs.COUNT() LOOP
23 Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
24 END LOOP;
25 END BEFORE STATEMENT;
26
27 AFTER EACH ROW IS
28 BEGIN
29 IF :NEW.Sal - :Old.Sal >
30 Twelve_Percent*Department_Avg_Salaries(:NEW.Deptno)
31 THEN
32 Raise_Application_Error(-20000, 'Raise too large');
33 END IF;
34 END AFTER EACH ROW;
35 END Check_Raise_On_Avg;
36 /
Trigger created.
/
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
SQL>
SQL> update emp set sal=sal*1.10 where empno = 7369;
1 row updated.
SQL>
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 880
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> update emp set sal=sal*1.08 where deptno = 20;
5 rows updated.
SQL>
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 864
7499 1600
7521 1250
7566 3213
7654 1250
7698 2850
7782 2450
7788 3240
7839 5000
7844 1500
7876 1188
7900 950
7902 3240
7934 1300
14 rows selected.
SQL>
SQL> rollback;
Rollback complete.
SQL>
Does the trigger reject raises? It certainly does:
SQL> update emp set sal=sal*1.10 where deptno = 30;
update emp set sal=sal*1.10 where deptno = 30
*
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'
SQL>
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=sal*1.10 where empno = 7698;
update emp set sal=sal*1.10 where empno = 7698
*
ERROR at line 1:
ORA-20000: Raise too large
ORA-06512: at "BING.CHECK_RAISE_ON_AVG", line 30
ORA-04088: error during execution of trigger 'BING.CHECK_RAISE_ON_AVG'
SQL>
SQL> select empno, sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
7900 950
7902 3000
7934 1300
14 rows selected.
SQL>
The first rejected update unfortunately disallowed all of the raises based on the failure of a few; the second update shows one employee where a 10 percent raise would be greater than 12 percent of the departmental average salary. Of course it is usually rare to see such a large raise given throughout an entire department so such occurrences would be few as raises are usually processed (outside of cost-of-living adjustments) on an individual basis.
Please note that doing the above in a traditional trigger would have resulted in a mutating table error since the table being updated cannot be queried during the update; all successful raises were processed and no such error was thrown.
Compound triggers are a nice addition to an already robust database system; they may not be commonplace but having them available certainly makes application development simpler as business rules that may be unenforceable using a regular trigger can be successfully implemented. They may be considered as ‘specialty tools’ in the database realm but remember that plumbers, builders and mechanics also have tools they only use once in a while and when the situation arises where a compound trigger can be useful it’s good to have them around.
