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 in Oracle 11g

Discussion in 'SQL PL/SQL' started by SBH, Dec 6, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview
    Oracle 11g came up with multiple enhancements for Developers. One of the significant among them was the introduction of Compound Triggers. The article explains the use of Compund Trigges and related notes.

    2. Introduction

    Compound triggers are one of the latest enhancements made in Oracle 11g. They resolve most of the restrictions currently associated with the triggers. It combines all the trigger timings i.e. i.e. before statement and each row, after statement and each row into one compound trigger.

    3. Compound Trigger features

    • Each Trigger time is a separate block in the Trigger body
    • Compound Trigger supports common state of PL/SQL variables till the execution of an event
    • Successfully resolves the mutating table error

    4. Syntax

    4.1. Compound Trigger Structure for Tables

    Code (Text):
    CREATE OR REPLACE TRIGGER [TRIGGER NAME]
    FOR [DML] ON [TABLE NAME]
    COMPOUND TRIGGER
    -- Initial section
       -- Declarations
       -- Subprograms
    Optional section
    BEFORE STATEMENT IS
    …;
    Optional section
    AFTER STATEMENT IS
    …;
    Optional section
    BEFORE EACH ROW IS
    …;
    Optional section
    AFTER EACH ROW IS
    …;
    END;
    4.2. Compound Trigger Structure for Views

    Code (Text):
    CREATE OR REPLACE TRIGGER [TRIGGER NAME]
    FOR [EVENT TYPE] ON [VIEW NAME]
    COMPOUND TRIGGER
    -- Initial section
       -- Declarations
       -- Subprograms
    -- Optional section (exclusive)
    INSTEAD OF EACH ROW IS
    ...;
    END;

    5. Compound Trigger Restrictions

    • Below are the guidelines to use Compound triggers.
    • Compound Trigger can only behave as DML trigger
    • Exception must be handled for each event block.
    • OLD and NEW identifiers can appear in row level timing blocks

    6. Mutating Table

    A Trigger must follow certain rules to maintain database purity. A DML trigger body must not query the affected row of the table or same triggering statement (mutually non exclusive) on the same table, which would result into a recursive triggering event; hence raising the exception ORA-4091. If such situation occurs, the table used is known as Mutating Table.

    Mutating Table: Illustration

    Trigger T_GET_SAL fires on update of salary in EMP table and attempt to select salary of employee 104 from EMP table in the body. Now, when an employee record is updated, snapshot of EMP table is floating. As a result o UPDATE event, trigger fires but attempts to query the same EMP table again. In this state, EMP table results into a Mutating table.

    Code (SQL):
    CREATE OR REPLACE TRIGGER T_GET_SAL
    AFTER UPDATE OF SALARY ON EMPLOYEE
    FOR EACH ROW
    DECLARE
       L_GETSAL NUMBER;
    BEGIN
    SELECT SALARY
    INTO L_GETSAL
    FROM EMPLOYEE
    WHERE EMPLOYEE_ID =110;


    END;
    /

    TRIGGER created.

    UPDATE EMPLOYEE
    SET SALARY = SALARY + 1500
    WHERE EMPLOYEE_ID = 110;

    UPDATE EMPLOYEE
           *
    ERROR at line 1:
    ORA-04091: TABLE SCOTT.EMPLOYEE IS mutating, TRIGGER/FUNCTION may NOT see it
    ORA-06512: at "SCOTT.T_GET_SAL", line 4
    ORA-04088: error during execution OF TRIGGER SCOTT.T_GET_SAL'
    Compound Trigger Solution

    Code (SQL):
    CREATE OR REPLACE TRIGGER TRG_GET_SAL
      FOR INSERT OR UPDATE OF SALARY
      ON EMPLOYEE
      COMPOUND TRIGGER
      TYPE SAL_TYP IS TABLE OF EMPLOYEE.SALARY%TYPE;
      EMP_SAL   SAL_TYP;
    BEFORE STATEMENT IS
      BEGIN
        SELECT SALARY
        BULK COLLECT
        INTO EMP_SAL
        FROM EMPLOYEE
        WHERE EMPLOYEE_ID = 110;
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
    BEGIN



      END AFTER EACH ROW;
    END TRG_GET_SAL;

    SQL> UPDATE EMPLOYEE
         SET SALARY = SALARY + 1500
         WHERE EMPLOYEE_ID = 110;

    1 ROW updated.
    In the above trigger, BEFORE STATEMENT populates the PL/SQL table with the current salary using a SELECT query. Then, in after row trigger, its value can be fetched and the Net increment can be calculated and displayed.