Compund Triggers in Oracle 11g

In one of our previous blog post, we had covered the basic concepts of triggers in Oracle. We had briefly touched upon the topic of compound triggers in that post. In 11g, Oracle introduced the concept of compound triggers in which we can combine more than one trigger.

Compound triggers allow us to combine more than one trigger with different triggering timings into a single compound trigger. This is the biggest advantage of compound trigger as it allows for modular programming with less cumbersome code. Compound trigger contains declaration section and more than one section for each timing points (i.e. before each row, after each row, after statement etc.).

In our example, we will create two tables. TEST and AUD_TEST (To record the auditing of inserted records). We are also creating sequence to generate the number automatically.

CREATE TABLE TEST
(
TEST_ID NUMBER(9),
TEST_NAME VARCHAR(30)
)
/

CREATE TABLE AUD_TEST
(
AUD_ACTION VARCHAR(6),
AUD_DATE DATE,
TEST_ID NUMBER(9),
TEST_NAME VARCHAR(30)
)
/

CREATE SEQUENCE TEST_SEQ
START WITH 100
INCREMENT BY 1
/

Now we will create a compound trigger with three triggering events.
• BEFORE EACH ROW: to generate the new ID
• AFTER EACH ROW: to bulk collect the inserted record into collection.
• AFTER STATEMENT: To populate audit tables using newly inserted records into TEST table. This is so that we don’t pay penalty of performance by inserting record for each row.

Let’s create the compound trigger now.

CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR INSERT
ON TEST
COMPOUND TRIGGER

/* Declaration Section*/

TYPE ga_Test_ID IS TABLE OF TEST.TEST_ID%TYPE index by pls_integer;
TYPE ga_Test_NAME IS TABLE OF TEST.TEST_NAME%TYPE index by pls_integer;

va_Test_NAME ga_Test_NAME;
va_Test_ID ga_Test_ID;
v_cnt PLS_INTEGER := 0;

BEFORE EACH ROW IS
BEGIN

:NEW.TEST_ID := TEST_SEQ.NEXTVAL;

END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN

v_cnt := v_cnt + 1;
va_Test_ID(v_cnt) := :NEW.TEST_ID;
va_Test_Name(v_cnt) := :NEW.TEST_NAME;

END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN

FOR i IN 1..VA_TEST_ID.COUNT
LOOP
INSERT INTO AUD_TEST(AUD_ACTION,AUD_DATE,TEST_ID,TEST_NAME)
VALUES (‘INSERT’,sysdate, va_Test_ID(i), va_Test_Name(i));
END LOOP;

END AFTER STATEMENT;

END TEST_TRIG_COMPOUND;
/

Let us insert records into the TEST table now.

SQL> INSERT INTO TEST(TEST_NAME)
2 SELECT object_name
3 FROM user_objects
4 WHERE rownum < 10;

9 rows created.

Above SQL indicates that we created 9 records successfully in TEST table and AUD_TEST table each. You can verify the result by querying both the tables.

Inspite of some obvious benefits, compound trigger also has certain limitations. Major limitations are:

• It can be defined only as a DML trigger.
• Exceptions cannot be handled across the blocks. It should be handled in the same block in which it occurs.
• :OLD and :NEW cannot be used in declaration section or BEFORE STATEMENT and AFTER STATEMENT section.
• :NEW values can be changed only in BEFORE EACH ROW section.

For complete list of restrictions, please refer to PLSQL Reference manual. In one of the upcoming blog post, we will see how we can use compound triggers to avoid mutating table errors.