External Tables in Oracle 10g/11g Database: Part 1

Using Triggers and Compound Triggers in Oracle 11g

By Contributing Author on June 16, 2011 in Database Development, Oracle Database

In database terminology, triggers are the objects which demonstrate the logic of schedule and automation.

Triggers are automatically invoked at a defined event and timing without any explicit calls. The logic embedded within the trigger and invocation must be well directed and synchronized to maintain database purity.

Oracle 11g made remarkable enhancements in Database Triggers. These enhancements and additions have transformed triggers into a logical, stable, and comprehensive database platform. In the past versions, developers used to face issues under certain conditions as listed below.

• Multiple triggers for single timing
• Mutating table confrontation
• Control the trigger execution by enabling and disabling it

Introduction of compound triggers which triggers different logic at different timings, guaranteed execution sequence of triggers and enable/disable functionalities make the PL/SQL a more efficient language and require less coding.

The article projects the transformations undergone by database triggers in 11g release of Oracle.

Trigger Enhancements

Setting the Trigger order

Earlier, if a DML event demands multiple triggering actions without altering the existing code, developers used to create multiple triggers for same timing and event. For example, a table EMPLOYEE can have two AFTER UPDATE OF EMPLOYEE FOR EACH ROW triggers.

Such activity was possible since Oracle 8i, but usually the trigger timing determines the order of its execution but the aforesaid baffling situation gives the complete privilege to the database server to determine the execution order of the triggers i.e. random execution. It remained a ‘crossed fingers’ situation for the developers to predict the execution order. Unsorted execution of DML triggers can produce unexpected results where setting or initializations of parameters are involved.

Now, oracle has released the flexibility to set the sequence of execution of triggers for the same triggering event and timing. Two new keywords FOLLOWS and PRECEDES have been introduced to force the triggers to follow set execution order.

Example Syntax [1]

CREATE OR REPLACE TRIGGER [TRIGGER TO FOLLOW]
[TIMING SPECIFICATION]
[EVENT SPECIFICATION]
FOLLOWS [TRIGGER TO BE FOLLOWED]

Example Code [1]: Demonstrate the use of FOLLOWS keyword

CREATE OR REPLACE TRIGGER T_UPD_SAL
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_SAL’);
INSERT INTO EMP_SAL_ARCHIVE
VALUES (EMPLOYEE_ID, DEPARTMENT, SALARY, HIKE_PERC, HIKE_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:NEW.SALARY,
((:NEW.SALARY-:OLD.SALARY)/:OLD.SALARY)*100,
SYSDATE)
END;

CREATE OR REPLACE TRIGGER T_UPD_JOB
AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
FOLLOWS T_UPD_SAL
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Executing T_UD_JOB’);
INSERT INTO EMP_JOB_ARCHIVE
VALUES(EMPLOYEE_ID, DEPARTMENT, OLD_JOB, NEW_JOB, PROM_DATE)
(:OLD.EMPLOYEE_ID,
:OLD.DEPARTMENT_ID,
:OLD.JOB_ID,
:NEW.JOB_ID,
SYSDATE);
END;

Now, the triggers would be executed in the set order. This ensures the logical and sorted execution of modular logic in the application.

DISABLED Triggers

Prior to Oracle 11g, by default all triggers used to get created in ENABLED state. In real time production systems, this behavior had rooted out reluctant and unavoidable issues. Suppose the database support team applies a production patch, which contains a new trigger script. Upon execution, the trigger script raises error due to some missing reference. If somehow the trigger could have been only created with compilation errors, but not on live association with the event [table], the error could be resolved out.

Oracle 11g resolves such scenarios by allowing a trigger to be created in DISABLED state. Note that state is not at all new for triggers. Earlier too, a trigger can be disabled using ALTER TRIGGER command. Oracle 11g has widened this feature by taking it at creation level. A disabled trigger can be enabled at any point of time in the program.

Example Code [2]: Demonstration of DISABLED trigger

CREATE OR REPLACE TRIGGER T_CRT_DIS
BEFORE INSERT ON ORDER
FOR EACH ROW
DISABLED
BEGIN
SELECT 1
INTO L_EXIST
FROM WAREHOUSE
WHERE ITEM_CODE = :NEW.PROD_CODE;
DBMS_OUTPUT.PUT_LINE(‘Product in stock: Go ahead’);
EXCEPTION
WHEN NO_DATA_FOUND THE RAISE_APPLICATION_ERROR(-20100, ‘Shortage of the product: No Order can be placed’);

DML triggers are now faster

DML Triggers in Oracle 11g are comparatively faster than their counterparts in earlier versions. The performance can be clearly measured by running a trigger separately in 10g and 11g and recording their execution time. I have executed a trigger T_CHECK_ORDER in Oracle 10g and 11g separately. Their execution time upon operation is recorded as 153

Figuire shows the reduction in execution time of the DML triggers in Oracle 11g.

New Language Features

 Compound Triggers

Compound triggers are another new tool in the kit to support reduced coding and interactivity. It combines all four triggering events into a single piece of code. Besides the coding efficiency, it tackles some bigger issues in the picture.

• Mutating table (ORA-04091) scenarios
• Suppose an EACH ROW triggers do some transaction in some other table using new values.

 Imagine the situation when dealing with millions of data and double transaction for each row through trigger action would cause huge losses to performance.

Earlier, workaround solutions do existed for the above problems, but with loads of complex coding using collections and multiple triggers. It used to become nightmare for developers to simulate and test such scenarios.

Compound triggers readily deal with all above scenarios in an efficient and interactive manner. The new trigger feature not only boosts up the performance during bulk operations, but also holds the state of its variables and member constructs till the execution of the statement. They are reset only at the beginning of new statement. Note that compound trigger is an optional feature. Separate DML triggers for different timing can still be created in earlier fashion.

On the lower side, compound triggers are only available for DML triggers. DDL and system level triggers still follow the old convention. In addition, condition based and autonomous compound triggers are not supported. An important point to mention here is the exception handling. It has to be handled explicitly in all the timing blocks, and not in the trigger body.

Syntax

The compound trigger syntax contains the man body and four blocks representing four timings associated with DML events. Note the COMPOUND TRIGGER keyword to differentiate with the other database triggers.

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;
For database views
INSTEAD OF EACH ROW IS
…;
END;

 Usage guidelines

1. Each timing handler block must appear only once in the compound trigger body
2.  All timing handler blocks are optional
3. Compound Trigger metadata can be captured in USER_TRIGGERS view. The new columns added are as below:

 

A new trigger type ‘COMPOUND’ would be updated for compound triggers. Based on availability of the timing block in the trigger body,

Illustrations of Compound Triggers

 Compound trigger as performance savior

I shall site a scenario where concurrent loading into a table is achieved through Compound trigger. Bulk loading uses an associative array to hold the data to be loaded. The loading is done after the statement execution completes. Refer the trigger code below

Example Code [3]: Compound trigger to implement bulk loading

CREATE OR REPLACE TRIGGER TRG_INS_ORDER
FOR INSERT ON ORDERS
COMPOUND TRIGGER
TYPE ORDER_T IS TABLE OF ORDER_ARCHIVE%ROWTYPE
INDEX BY PLS_INTEGER;
L_ORDERS ORDER_T;
I NUMBER := 0;
AFTER EACH ROW IS
BEGIN
I := I+1;
L_ORDERS(I).ORD_ID := :NEW.ORD_ID;
L_ORDERS(I).ORD_CODE := :NEW.ITEM_CODE;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Statement level loading’);
FORALL J IN 1..L_ORDERS.COUNT
INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
L_ORDERS.DELETE;
I := 0;
END AFTER STATEMENT;
END;
/
Trigger created.

–Inserting test data into ORDERS table

INSERT INTO ORDERS(ORD_ID, ITEM_CODE)
SELECT ORD_SEQ.NEXTVAL, ‘PROD ‘||LEVEL
FROM DUAL
CONNECT BY LEVEL < 1000;
Statement level loading
999 rows created.

To be noticed, the simultaneous insertion process has been carried out in bulk. This pulls up the performance to a major extent. If the same insert operation have had used conventional row level trigger, it would have shrunk the performance by 1000 insert statements.

Resolving Table mutation scenarios

Mutating table scenarios have been hovering over the multi-user based applications for a long time. Several times, tedious workaround solutions and unreliability has forces the architects to modify the design, so as to avoid table mutation during parallel real time processing. Compound triggers in Oracle 11g provide concrete logic base to deal with such scenarios. The trigger example below shows how they handle and achieve such events.

Before moving to the solution, first I will simulate the table mutation scenario.

The row level trigger TRG_INS_ORDERS on ORDERS table will display the updated item code.

CREATE OR REPLACE TRIGGER TRG_INS_ORDERS
AFTER UPDATE OF ITEM_CODE ON ORDERS
FOR EACH ROW
DECLARE
L_ITEM VARCHAR2(200);
BEGIN
SELECT ITEM_CODE INTO L_ITEM FROM ORDERS WHERE ORD_ID = :OLD.ORD_ID;
DBMS_OUTPUT.PUT_LINE(‘ITEM CODE CHANGED FROM ‘||:OLD.ITEM_CODE||’ TO ‘||L_ITEM);
* END;
SQL> /
Trigger created.

UPDATE ORDERS
SET ITEM_CODE=’ITEM:’||ORD_ID
WHERE ORD_ID=10;
update orders
*
ERROR at line 1:
ORA-04091: table SCOTT.ORDERS is mutating, trigger/function may not see it
ORA-06512: at “SCOTT.TRG_INS_ORDERS “, line 4
ORA-04088: error during execution of trigger ‘SCOTT.TRG_INS_ORDERS’

The table remains in the floating state, when the trigger TRG_INS_ORDERS tries to select and display the changed value. Such situations are well handled using compound triggers. Check the example below.

CREATE OR REPLACE TRIGGER TRG_INS_ORDERS
FOR UPDATE ON ORDERS
COMPOUND TRIGGER
TYPE ITEM_CODE_T IS TABLE OF ORDERS.ITEM_CODE%TYPE INDEX BY BINARY_INTEGER;
L_ITEM_CODE ITEM_CODE_T;
BEFORE STATEMENT IS
CURSOR C IS
SELECT ORD_ID, ITEM_CODE
FROM ORDERS;
BEGIN
FOR I IN C
LOOP
L_ITEM_CODE(I.ORD_ID):=I.ITEM_CODE;
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Item code changed from ‘||L_ITEM_CODE(:OLD.ORD_ID)||’ to ‘||:NEW.ITEM_CODE);
END AFTER EACH ROW;
END;
/

UPDATE ORDERS
SET ITEM_CODE = ‘Item Number:’||ORD_ID
WHERE ORD_ID=100;
Item code changed from Prod 99 to Item Number:100
1 row updated.