Compound Triggers in Oracle 11g – Tutorial with example

Compound Triggers in Oracle 11g

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 characterise 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;
Code language: SQL (Structured Query Language) (sql)

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-initialised, 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);
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Result:

Code language: Bash (bash)

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');
Code language: SQL (Structured Query Language) (sql)

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 :-)

Get our Articles via Email. Enter your email address.

You may also like...

5 Comments

  1. John says:

    The compound trigger above throws ten errors PLS-00302 components (emp_id, action, from_value, etc.) must be declared.

    Even after adding language in the declaration area (following v_user), the same errors appear…. Would you have any thoughts on that? Could you supply a script correction?

    Thank you !!

  2. karthik says:

    Corrected script for table aud_emp
    CREATE TABLE aud_emp
    (
    emp_id NUMBER,
    upd_by varchar2(50) NOT NULL,
    upd_dt date NOT NULL,
    field varchar2(50) NOT NULL,
    from_value varchar2(50) NOT NULL,
    to_value varchar2(50) NOT NULL,
    action VARCHAR2(50)
    );

    • raju says:

      thank you bro

  3. Robhit Saxena says:

    Hi,

    I have a question :

    Suppose I have a procedure which inserts data in one table in for loop i in 1..100000
    loop
    on this table I have set compound trigger .
    The execution flow for each insert is:
    1. Firstly If it is inserting than v_index gets populated with :NEW values
    2. It checks for v_index >= v_threshhold
    3. Than After Statement the flush_logs procedure is called to insert into new tableusing forall

    Due to these above steps/flow it is actually taking time for insert and if you compare normal trigger execution it is faster than this compound trigger.,

    Please suggest….

  4. Taylor says:

    is v_count declared implicitly or should it be v_index? i don’t quite follow how v_count is working…

Leave a Reply

Your email address will not be published. Required fields are marked *