Features of Oracle 11g

                                                                                                                                 -Ramprasad Pedapatnam

Compound Triggers

 

A compound trigger is actually four different triggers defined as one. For instance, an UPDATE compound trigger has a before statement, before row, after statement, and after row all rolled into one compound trigger. This a single piece of code, so we can pass variables just like any other monolithic PL/SQL code.
Consider a hotel database: bookings for the hotel rooms are recorded in the table named BOOKINGS. We also want to record the changes to this table to a tracking table—sort of like auditing, but with a twist: We want to make it transactional. Triggers are perfect for that.
We come up with a small after-update row trigger that records the old and new values along with who changed it into a table BOOKINGS_HIST. So far, so good.
But there is a little issue here. The after-update row trigger fires for every row, and some bookings are changed in bulk, updating hundreds of rows in one transaction. Separate after-update-row triggers fire for each of these rows and each execution inserts a record into the bookings_hist table, so performance is not optimal.
A better approach may be to batch these inserts and insert them in bulk to the bookings_hist table as well. We can accomplish that using a complex series of triggers. The trick is to put the values to be placed in the bookings_hist table in a collection in the row trigger and then load the data from the collection to the bookings_hist table in the after-update-statement trigger, which fires only once. As the actual insert happens only once, the process is faster than inserting on each row.
But these are two different triggers in separate pieces of code. The only way to pass a collection variable from one trigger to the other is to create a package with a collection variable such as VARRAY or PL/SQL TABLE in the package specification, populate it on the after-update row trigger, and read in the after-statement trigger—no easy task. Instead, wouldn't it be simpler if you could place all the triggers in one piece of code?
In Oracle Database 11g we can, using compound triggers.

Let's consider an example.

     1  create or replace trigger tr_bookings_track

     2  for update of booking_dt

     3  on bookings

     4  compound trigger

     5      type ty_bookings_hist is table of bookings_hist%rowtype

     6          index by pls_integer;

     7      coll_bookings_hist          ty_bookings_hist;

     8      ctr                         pls_integer := 0;

     9  before statement is

    10  begin

    11      dbms_output.put_line('In before statement');

    12  end before statement;

    13  before each row is

    14  begin

    15      dbms_output.put_line('In before each row');

    16  end before each row;

    17  after each row is

    18  begin

    19      ctr := ctr + 1;

    20      dbms_output.put_line('In after each row.

                booking_id='||:new.booking_id);

    21      coll_bookings_hist(ctr).booking_id := :new.booking_id;

    22      coll_bookings_hist(ctr).mod_dt := sysdate;

    23      coll_bookings_hist(ctr).mod_user := user;

    24      coll_bookings_hist(ctr).old_booking_dt := 

                     :old.booking_dt;

    25      coll_bookings_hist(ctr).new_booking_dt := 

                    :new.booking_dt;

    26  end after each row;

    27  after statement is

    28  begin

    29      dbms_output.put_line('In after statement');

    30      forall counter in 1..coll_bookings_hist.count()

    31          insert into bookings_hist

    32          values coll_bookings_hist(counter);

    33  end after statement;

    34  end tr_bookings_track;

To better understand the workings of the trigger, let's do a sample update, which updates four rows.

update bookings

set booking_dt = sysdate

where booking_id between 100 and 103;

Here is the output:

In before statement

In before each row

In after each row. booking_id=100

In before each row

In after each row. booking_id=101

In before each row

In after each row. booking_id=102

In before each row

In after each row. booking_id=103

In after statement

Note how the compound trigger operates. Roughly, it has four sections:

Before Statement
... executes once before the statement ...
Before Row
... executes once per row before the action ...
After Row
... executes once per row after the action ...
After Statement
... executes once per statement ...
As you'll see, this code is monolithic but each section executes at different points.
In the previous example, we placed dbms_output statements at various points to show how each section executes along what points. We updated four rows, with booking_ids 100, 101, 102, and 103, and you can see it called the before- and after-statement triggers once each and the row triggers (before and after) once per row. (In the previous example, there is no need for before-statement or -row triggers but we have placed them there to illustrate the functionality.)
If you look into the table bookings_hist, you will see that there are now four records—one for each booking_id—but these four records were inserted in bulk at the end of the statement, not for each row updated:

Booking_id	Mod_Dt	Mod_user	Old_book1	New_book1
100	12-Jul-10	pedapar	11-Jun-09	12-Jul-10
101	12-Jul-10	pedapar	10-May-09	12-Jul-10
102	12-Jul-10	pedapar	20-May-09	12-Jul-10
103	12-Jul-10	pedapar	11-Jun-09	12-Jul-10

One really useful thing about compound triggers is that stateful objects in PL/SQL code, such as variables, packages and so on, are instantiated when the trigger is fired and at the end of the trigger firing, the state is erased clean. In the above example, you can see that I have neither initialized the collection nor deleted the contents from the collection. All this is done automatically without my intervention