The new Compound Trigger make it easier to handle a situation, where we want trigger fired at different point of time to share common data.

Before Compound trigger, to handle such situation we required the use of packages variable so that data can be shared. But this approach gives rise to many complications such as leaking of data from one transaction to another, fragmentation of logic, increase in number of packages and triggers and so on.

Package variable has a scope of session, which may lead to leaking of data from one transaction to another transaction so this need to be taken care manually. Apart from this, maintenance due to increase in number of triggers and packages becomes more complicated. Logical becomes very fragmented as it is spread among number of trigger and packages.

In words of Oracle Documentation

“To achieve the same effect with simple triggers, you had to model the common state with an ancillary package. This approach was both cumbersome to program and subject to memory leak when the triggering statement caused an error and the after-statement trigger did not fire”

The new compound trigger has a declaration section and a section for each of its timing points. All of these sections can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error.

A compound trigger has a declaration section and must have at least one timing-point section.

The declaration section (the first section) declares variables and subprograms that timing-point sections can use. When the trigger fires, the declaration section executes before any timing-point sections execute. Variables and subprograms declared in this section have firing-statement duration.

A compound trigger defined on a table has one or more of the timing-point sections described as follows:

• Before the triggering statement executes – BEFORE STATEMENT
• After the triggering statement executes – AFTER STATEMENT
• Before each row that the triggering statement affects – BEFORE EACH ROW
• After each row that the triggering statement affects – AFTER EACH ROW

Compound Trigger Skelton code is as follows:

CREATE TRIGGER compound_trigger_jvitm
FOR UPDATE OF amount ON journal_voucher_items
COMPOUND TRIGGER

– Declaration Section
– Variables declared here have firing-statement duration.
Jv_type CONSTANT VARCHAR2(1):=’S’;

BEFORE STATEMENT IS
BEGIN
…
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
…
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
…
END AFTER EACH ROW;
END compound_trigger_jvitm;
/

Few Exceptions:

• The body of a compound trigger must be a compound trigger block.
• A compound trigger must be a DML trigger.
• A compound trigger defined on a view has an INSTEAD OF EACH ROW timing-point section, and no other timing-point section.
• If the triggering statement affects no rows, and the compound trigger has neither a BEFORE STATEMENT section nor an AFTER STATEMENT section, the trigger never fires.
• The firing order of compound triggers is not guaranteed. Their firing can be interleaved with the firing of simple triggers.
• 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.
• :OLD, :NEW, and ARENT cannot appear in the declaration section, the BEFORE STATEMENT section, or the AFTER STATEMENT section.
• The declaration section cannot include PRAGMA AUTONOMOUS_TRANSACTION.
• Only the BEFORE EACH ROW section can change the value of :NEW.

Reference Oracle Manual