Compund Triggers in Oracle 11g
Posted by decipherinfosys on March 24, 2008
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
CREATE TABLE AUD_TEST
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
/* 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;
v_cnt PLS_INTEGER := 0;
BEFORE EACH ROW IS
:NEW.TEST_ID := TEST_SEQ.NEXTVAL;
END BEFORE EACH ROW;
AFTER EACH ROW IS
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
FOR i IN 1..VA_TEST_ID.COUNT
INSERT INTO AUD_TEST(AUD_ACTION,AUD_DATE,TEST_ID,TEST_NAME)
VALUES (‘INSERT’,sysdate, va_Test_ID(i), va_Test_Name(i));
END AFTER STATEMENT;
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.
2 Responses to “Compund Triggers in Oracle 11g”
Sorry, the comment form is closed at this time.