Compound Triggers In Oracle 11g – Tutorial With Example

In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points:

1. Before the firing statement
2. Before each row that the firing statement affects
3. After each row that the firing statement affects
4. After the firing statement

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable. This is because compound trigger in oracle 11g has a declarative section where one can declare variable to be used within trigger. This common state is established at the start of triggering statement and is destroyed after completion of trigger (regardless of trigger being in error or not). If same had to be done without compound-trigger, it might have been required to share data using packages.

When to use Compound Triggers

The compound trigger is useful when you want to accumulate facts that characterize the “for each row” changes and then act on them as a body at “after statement” time. Two popular reasons to use compound trigger are:

1. To accumulate rows for bulk-insertion. We will later see an example for this.
2. To avoid the infamous ORA-04091: mutating-table error.

Details of Syntax

CREATE OR REPLACE TRIGGER compound_trigger_name
FOR [INSERT|DELETE]UPDATE [OF column] ON table
COMPOUND TRIGGER
   -- Declarative Section (optional)
   -- Variables declared here have firing-statement duration.
     
     --Executed before DML statement
     BEFORE STATEMENT IS
     BEGIN
       NULL;
     END BEFORE STATEMENT;
   
     --Executed before each row change- :NEW, :OLD are available
     BEFORE EACH ROW IS
     BEGIN
       NULL;
     END BEFORE EACH ROW;
   
     --Executed aftereach row change- :NEW, :OLD are available
     AFTER EACH ROW IS
     BEGIN
       NULL;
     END AFTER EACH ROW;
   
     --Executed after DML statement
     AFTER STATEMENT IS
     BEGIN
       NULL;
     END AFTER STATEMENT;

END compound_trigger_name;

Note the ‘COMPOUND TRIGGER’ keyword above.

Some Restriction/Catches to note

1. The body of a compound trigger must be a compound trigger block.
2. A compound trigger must be a DML trigger.
3. A compound trigger must be defined on either a table or a view.
4. The declarative part cannot include PRAGMA AUTONOMOUS_TRANSACTION.
5. A compound trigger body cannot have an initialization block; therefore, it cannot have an exception section. This is not a problem, because the BEFORE STATEMENT section always executes exactly once before any other timing-point section executes.
6. An exception that occurs in one section must be handled in that section. It cannot transfer control to another section.
7. If a section includes a GOTO statement, the target of the GOTO statement must be in the same section.
8. OLD, :NEW, and :PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.
9. Only the BEFORE EACH ROW section can change the value of :NEW.
10. If, after the compound trigger fires, the triggering statement rolls back due to a DML exception:
    • Local variables declared in the compound trigger sections are re-initialized, and any values computed thus far are lost.
    • Side effects from firing the compound trigger are not rolled back.
11. The firing order of compound triggers is not guaranteed. Their firing can be interleaved with the firing of simple triggers.
12. If compound triggers are ordered using the FOLLOWS option, and if the target of FOLLOWS does not contain the corresponding section as source code, the ordering is ignored.

Example: Using Compound Triggers in Table Auditing

Hopefully this example with make things more clear. Lets create a compound trigger for auditing a large table called ‘employees’. Any changes made in any field of ‘employees’ table needs to be logged in as a separate row in audit table ‘aud_empl’.
Since each row update in employees table needs to make multiple inserts in the audit table, we should consider using a compound trigger so that batching of inserts can be performed.

But before that we need to create our Tables:

--Target Table
CREATE TABLE employees(
    emp_id  varchar2(50) NOT NULL PRIMARY KEY,
    name    varchar2(50) NOT NULL, 
    salary  number NOT NULL
);

--Audit Table
CREATE TABLE aud_emp(
    upd_by    varchar2(50) NOT NULL, 
    upd_dt    date NOT NULL,
    field     varchar2(50) NOT NULL, 
    old_value varchar2(50) NOT NULL,
    new_value varchar2(50) NOT NULL);

Now the trigger…
On update of each row instead of performing an insert operation for each field, we store (buffer) the required attributes in a Arrays of type aud_emp. Once a threshold is reached (say 1000 records), we flush the buffered data into audit table and reset the counter for further buffering.
And at last, as part of AFTER STATEMENT we flush any remaining data left in buffer.

--Trigger
CREATE OR REPLACE TRIGGER aud_emp
FOR INSERT OR UPDATE
ON employees
COMPOUND TRIGGER
  
  TYPE t_emp_changes       IS TABLE OF aud_emp%ROWTYPE INDEX BY SIMPLE_INTEGER;
  v_emp_changes            t_emp_changes;
  
  v_index                  SIMPLE_INTEGER       := 0;
  v_threshhold    CONSTANT SIMPLE_INTEGER       := 1000; --maximum number of rows to write in one go.
  v_user          VARCHAR2(50); --logged in user
  
  PROCEDURE flush_logs
  IS
    v_updates       CONSTANT SIMPLE_INTEGER := v_emp_changes.count();
  BEGIN

    FORALL v_count IN 1..v_updates
        INSERT INTO aud_emp
             VALUES v_emp_changes(v_count);

    v_emp_changes.delete();
    v_index := 0; --resetting threshold for next bulk-insert.

  END flush_logs;

  AFTER EACH ROW
  IS
  BEGIN
        
    IF INSERTING THEN
        v_index := v_index + 1;
        v_emp_changes(v_index).upd_dt       := SYSDATE;
        v_emp_changes(v_index).upd_by       := SYS_CONTEXT ('USERENV', 'SESSION_USER');
        v_emp_changes(v_index).emp_id       := :NEW.emp_id;
        v_emp_changes(v_index).action       := 'Create';
        v_emp_changes(v_index).field        := '*';
        v_emp_changes(v_index).from_value   := 'NULL';
        v_emp_changes(v_index).to_value     := '*';

    ELSIF UPDATING THEN
        IF (   (:OLD.EMP_ID <> :NEW.EMP_ID)
                OR (:OLD.EMP_ID IS     NULL AND :NEW.EMP_ID IS NOT NULL)
                OR (:OLD.EMP_ID IS NOT NULL AND :NEW.EMP_ID IS     NULL)
                  )
             THEN
                v_index := v_index + 1;
                v_emp_changes(v_index).upd_dt       := SYSDATE;
                v_emp_changes(v_index).upd_by       := SYS_CONTEXT ('USERENV', 'SESSION_USER');
                v_emp_changes(v_index).emp_id       := :NEW.emp_id;
                v_emp_changes(v_index).field        := 'EMP_ID';
                v_emp_changes(v_index).from_value   := to_char(:OLD.EMP_ID);
                v_emp_changes(v_index).to_value     := to_char(:NEW.EMP_ID);
                v_emp_changes(v_index).action       := 'Update';
          END IF;
        
        IF (   (:OLD.NAME <> :NEW.NAME)
                OR (:OLD.NAME IS     NULL AND :NEW.NAME IS NOT NULL)
                OR (:OLD.NAME IS NOT NULL AND :NEW.NAME IS     NULL)
                  )
             THEN
                v_index := v_index + 1;
                v_emp_changes(v_index).upd_dt       := SYSDATE;
                v_emp_changes(v_index).upd_by       := SYS_CONTEXT ('USERENV', 'SESSION_USER');
                v_emp_changes(v_index).emp_id       := :NEW.emp_id;
                v_emp_changes(v_index).field        := 'NAME';
                v_emp_changes(v_index).from_value   := to_char(:OLD.NAME);
                v_emp_changes(v_index).to_value     := to_char(:NEW.NAME);
                v_emp_changes(v_index).action       := 'Update';
          END IF;
                       
        IF (   (:OLD.SALARY <> :NEW.SALARY)
                OR (:OLD.SALARY IS     NULL AND :NEW.SALARY IS NOT NULL)
                OR (:OLD.SALARY IS NOT NULL AND :NEW.SALARY IS     NULL)
                  )
             THEN
                v_index := v_index + 1;
                v_emp_changes(v_index).upd_dt      := SYSDATE;
                v_emp_changes(v_index).upd_by      := SYS_CONTEXT ('USERENV', 'SESSION_USER');
                v_emp_changes(v_index).emp_id      := :NEW.emp_id;
                v_emp_changes(v_index).field       := 'SALARY';
                v_emp_changes(v_index).from_value  := to_char(:OLD.SALARY);
                v_emp_changes(v_index).to_value    := to_char(:NEW.SALARY);
                v_emp_changes(v_index).action      := 'Update';
          END IF;
                       
    END IF;

    IF v_index >= v_threshhold THEN
      flush_logs();
    END IF;

   END AFTER EACH ROW;

  -- AFTER STATEMENT Section:
  AFTER STATEMENT IS
  BEGIN
     flush_logs();
  END AFTER STATEMENT;

END aud_emp;
/

INSERT INTO employees VALUES (1, 'emp1', 10000);
INSERT INTO employees VALUES (2, 'emp2', 20000);
INSERT INTO employees VALUES (3, 'emp3', 16000);

UPDATE employees 
   SET salary = 2000
 WHERE salary > 15000;

SELECT * FROM aud_emp;

Result:

EMP_ID,UPD_BY,UPD_DT,ACTION,FIELD,FROM_VALUE,TO_VALUE
1,Aditya,1/22/2014 10:59:33 AM,Create,*,NULL,*
2,Aditya,1/22/2014 10:59:34 AM,Create,*,NULL,*
3,Aditya,1/22/2014 10:59:35 AM,Create,*,NULL,*
2,Aditya,1/22/2014 10:59:42 AM,Update,SALARY,20000,2000
3,Aditya,1/22/2014 10:59:42 AM,Update,SALARY,16000,2000

Now any changes in any field of employees will to be written in aud_emp table. A beauty of this approach is we were able to access same data ‘v_emp_changes’ between statement and row triggering events.

With this in mind, one can see that it make sense to move v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER'); inside declarative(or BEFORE STATEMENT if complex computation) section as a pre-processing step. To do so, v_user variable declared in trigger body can be used and assigned value of logged in user in the declarative section itself. So that same computation is not made during after-each-row section, and is computed and stored in a variable just once before row-level execution begins.

--declarative section
v_user          VARCHAR2(50) := SYS_CONTEXT ('USERENV', 'SESSION_USER');

Similarly any such pre-processing if required can be performed on that source table (mutating table), doing so will avoid any possible mutating-error. For e.g., consider the same example with another restriction, “Any update of salary should be such that it is not less than 1/12th of maximum salary of any employee, or else an error is raised”. To do this, it will be needed to get the maximum value of salary in the ‘employees’ table, and such calculation can be made in BEFORE STATEMENT section and stored in variable.

Hope that helped to have a better understanding of Compound Triggers in Oracle.
The End :-)

Oracle 11G New Feature: Virtual Column

Oracle 11g introduced the concept of ‘Virtual Column’ within a table. Virtual Columns are similar to normal table’s columns but with the following differences:

• They are defined by an expression. The result of evaluation of this expression becomes the value of the column.
• The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
• You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.

The syntax for defining a virtual column is:

column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]

where the parameters within [] are optional and can be omitted. If you don’t mention the datatype, Oracle will decide it based on the result of the expression.

Excepting the above points, a virtual column, exists just like any other column of a normal table and the following points apply to it:

1. Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
2. Statistics can be collected on them.
3. They can be used as a partition key in virtual column based partitioning.
4. Indexes can be created on them. As you might have guessed, oracle would create function based indexes as we create on normal tables.
5. Constraints can be created on them.

Create table with Virtual Column

For creating a virtual column, use the syntax mentioned above. Consider the following example:

CREATE TABLE EMPLOYEE (     empl_id        NUMBER,     empl_nm        VARCHAR2(50),     monthly_sal    NUMBER(10,2),     bonus          NUMBER(10,2),     total_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal*12 + bonus) );

Here we have defined a virtual column “total_sal” whose value would be dynamically calculated using the expression provided after the “generated always as” clause. Please note that this declaration is different than using “default” clause for a normal column as you can’t refer column names with “default” clause.

Lets check the data dictionary view:

SELECT column_name, data_type, data_length, data_default, virtual_column   FROM user_tab_cols  WHERE table_name = 'EMPLOYEE';   COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_DEFAULT             | VIRTUAL_COLUMN EMPL_ID     | NUMBER    | 22          | null                     | NO            EMPL_NM     | VARCHAR2  | 50          | null                     | NO            MONTHLY_SAL | NUMBER    | 22          | null                     | NO            BONUS       | NUMBER    | 22          | null                     | NO            TOTAL_SAL   | NUMBER    | 22          | "MONTHLY_SAL"*12+"BONUS" | YES

The value “YES” for the column “virtual_column” tells us that this is a virtual column. Another optional keyword “VIRTUAL” can also be added to make it syntactically complete.

DROP TABLE EMPLOYEE PURGE;   CREATE OR REPLACE FUNCTION get_empl_total_sal ( p_monthly_sal   NUMBER,                                                 p_bonus         NUMBER)    RETURN NUMBER DETERMINISTIC IS BEGIN    RETURN p_monthly_sal * 12 + p_bonus; END;   CREATE TABLE EMPLOYEE (empl_id     NUMBER,  empl_nm     VARCHAR2(50),  monthly_sal NUMBER(10,2),  bonus       NUMBER(10,2),  total_sal   NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) VIRTUAL );

We have included the “VIRTUAL” clause in the table definition. Please note that instead of using an expression, I have used a deterministic function. A deterministic function, when passed certain inputs, will always return the exact same output. “DETERMINISTIC” keyword is needed in order to mark a function as a candidate to be used in a function based index.

You can also create indexes on the virtual columns. Here is an example:

CREATE INDEX idx_total_sal ON employee(total_sal);   SELECT index_name, index_type   FROM user_indexes  WHERE table_name = 'EMPLOYEE';   INDEX_NAME     INDEX_TYPE                 IDX_TOTAL_SAL  FUNCTION-BASED NORMAL

Note that even this function is used as part of table definition, you can still drop it. But this in turn will make the table inaccessible.

DROP FUNCTION get_empl_total_sal;   SELECT * FROM employee; * Error at line 0 ORA-00904: "schema"."GET_EMPL_TOTAL_SAL": invalid identifier

You can alter the table with virtual column as you would modify a table with normal columns. Lets add the same column using the ALTER command:

DROP TABLE EMPLOYEE PURGE;   CREATE TABLE EMPLOYEE (empl_id     NUMBER,  empl_nm     VARCHAR2(50),  monthly_sal NUMBER(10,2),  bonus       NUMBER(10,2) );   ALTER TABLE EMPLOYEE ADD (total_sal AS (monthly_sal * 12 + bonus));

Note that the datatype of the new column is not declared. It will be assigned a datatype based on the result of the expression (in this case, it would be NUMBER). Now let’s insert some data in the table:

INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)    WITH DATA AS         (SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus            FROM DUAL          UNION          SELECT 200, 'BBB', 12000, 2000            FROM DUAL          UNION          SELECT 300, 'CCC', 32100, 1000            FROM DUAL          UNION          SELECT 400, 'DDD', 24300, 5000            FROM DUAL          UNION          SELECT 500, 'EEE', 12300, 8000            FROM DUAL)    SELECT *      FROM DATA;   SELECT * FROM employee;   EMPL_ID | EMPL_NM | MONTHLY_SAL | BONUS | TOTAL_SAL 100     | AAA     | 20000       | 3000  | 243000 200     | BBB     | 12000       | 2000  | 146000 300     | CCC     | 32100       | 1000  | 386200 400     | DDD     | 24300       | 5000  | 296600 500     | EEE     | 12300       | 8000  | 155600

Here we have populated the table columns except the virtual column with some values. Upon selecting the data, we get the value for “total_sal”. Remember that this data is not actually stored in the database but evaluated dynamically. Lets try updating this value of this virtual column:

UPDATE employee    SET total_sal = 2000;   ORA-54017: UPDATE operation disallowed on virtual columns

As mentioned before, the statistics can also be gathered for the virtual columns.

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'EMPLOYEE');   SELECT column_name, num_distinct,        display_raw (low_value, data_type)  low_value,        display_raw (high_value, data_type) high_value   FROM dba_tab_cols  WHERE table_name = 'EMPLOYEE';   COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE TOTAL_SAL   | 5            | 146000    | 386200 BONUS       | 5            | 1000      | 8000 MONTHLY_SAL | 5            | 12000     | 32100 EMPL_NM     | 5            | AAA       | EEE EMPL_ID     | 5            | 100       | 500

Limitations on Virtual Columns

**The query above uses a wonderful function “display_raw” by “Greg Rahn” to display the high/low values. Please check the references at the last to see it’s definition.

1. A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.
2. All columns mentioned as part of the virtual column expression should belong to the same table.
3. No DMLs are allowed on the virtual columns.
4. The virtual column expression can’t reference any other virtual column.
5. Virtual columns can only be created on ordinary tables. They can’t be created on index-organized, external, object, cluster or temporary tables.
6. If a deterministic function is used as virtual column expression, that virtual column can’t be used as a partitioning key for virtual column-based partitioning.

Virtual Column-Based Partitioning

Prior releases of Oracle only allowed a table to be partitioned based on a physical column. Oracle 11g, with the addition of virtual columns, now allows a partition key based on an expression, using one or more existing columns of the table. A virtual column can now be used as a partitioning key. Lets partition our table based on the virtual column “total_sal”:

DROP TABLE EMPLOYEE PURGE;   CREATE TABLE employee (empl_id     NUMBER,  empl_nm     VARCHAR2(50),  monthly_sal NUMBER(10,2),  bonus       NUMBER(10,2),  total_sal   NUMBER(10,2) AS (monthly_sal*12 + bonus) ) PARTITION BY RANGE (total_sal)     (PARTITION sal_200000 VALUES LESS THAN (200000),      PARTITION sal_400000 VALUES LESS THAN (400000),      PARTITION sal_600000 VALUES LESS THAN (600000),      PARTITION sal_800000 VALUES LESS THAN (800000),      PARTITION sal_default VALUES LESS THAN (MAXVALUE));   INSERT INTO employee (empl_id, empl_nm, monthly_sal, bonus)    WITH DATA AS         (SELECT 100 empl_id, 'AAA' empl_nm, 20000 monthly_sal, 3000 bonus            FROM DUAL          UNION          SELECT 200, 'BBB', 12000, 2000            FROM DUAL          UNION          SELECT 300, 'CCC', 32100, 1000            FROM DUAL          UNION          SELECT 400, 'DDD', 24300, 5000            FROM DUAL          UNION          SELECT 500, 'EEE', 12300, 8000            FROM DUAL)    SELECT *      FROM DATA;   EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'EMPLOYEE',granularity => 'PARTITION');   SELECT   table_name, partition_name, num_rows     FROM user_tab_partitions    WHERE table_name = 'EMPLOYEE' ORDER BY partition_name;   TABLE_NAME | PARTITION_NAME | NUM_ROWS EMPLOYEE   | SAL_200000     | 2 EMPLOYEE   | SAL_400000     | 3 EMPLOYEE   | SAL_600000     | 0 EMPLOYEE   | SAL_800000     | 0 EMPLOYEE   | SAL_DEFAULT    | 0

So far, everything looks fine, lets now try to update monthly salary of one employee and in turn the value of total_sal.

UPDATE employee    SET monthly_sal = 30000  WHERE empl_id = 500;   ORA-14402: updating partition key column would cause a partition change

What happened? The reason is simple, updating the “monthly_sal” would result into change in “total_sal” of the employee and thus a partition change is required. This can be handled by enabling the row movement in the current definition of the table.

ALTER TABLE employee ENABLE ROW MOVEMENT;   UPDATE employee    SET monthly_sal = 80000  WHERE empl_id = 500;   1 row updated.

The update works fine. As mentioned before, a deterministic function can’t be used as virtual column expression which is to be used as a partitioning key. It has to be an expression defined on the columns of the table as done in the previous example. The following syntax will result in oracle error:

CREATE TABLE employee_new (empl_id     NUMBER,  empl_nm     VARCHAR2(50),  monthly_sal NUMBER(10,2),  bonus       NUMBER(10,2),  total_sal   NUMBER(10,2) AS (get_empl_total_sal(monthly_sal, bonus)) ) PARTITION BY RANGE (total_sal)     (PARTITION sal_200000 VALUES LESS THAN (200000),      PARTITION sal_400000 VALUES LESS THAN (400000),      PARTITION sal_600000 VALUES LESS THAN (600000),      PARTITION sal_800000 VALUES LESS THAN (800000),      PARTITION sal_default VALUES LESS THAN (MAXVALUE));   ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns