1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Compound Triggers

Discussion in 'SQL PL/SQL' started by zargon, Nov 19, 2012.

  1. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle 11g offers a new twist on triggers, the compound trigger, a trigger than can act both before and after an update, insert or delete has occurred. This makes possible the abilty 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 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:
    <pre><span style="color:#3333ff;">
    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;
    /</span></pre>
    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:
    <pre><span style="color:#3333ff;">
    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></span></pre>
    Does the trigger reject raises? It certainly does:
    <pre><span style="color:#3333ff;">
    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>
    </span></pre>
    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.