Efficient PL SQL Coding Homework Help, Assignment Help, Online Tutor, Tutoring

Submit Assignment

* *

Payment Online Assignment Help

Asp dot net Project Homework

Homework Help

sfsf

Efficient PL SQL Coding

Compound Triggers

Consider a hotel database: bookings for the hotel rooms are recorded in the table named BOOKINGS. You also want to record the changes to this table to a tracking table—sort of like auditing, but with a twist: You want to make it transactional. Triggers are perfect for that.

You 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. You 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 you can, using 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 you can pass variables just like any other monolithic PL/SQL code.

Let's consider an example. The line numbers are added to aid explanation.

create or replace trigger tr_bookings_track
for update of booking_dt
on bookings
compound trigger
      type ty_bookings_hist is table of bookings_hist%rowtype
       index by pls_integer;
      coll_bookings_hist          ty_bookings_hist;
           ctr                         pls_integer := 0;
       before statement is
      begin
          dbms_output.put_line('In before statement');
      end before statement;
      before each row is
      begin
          dbms_output.put_line('In before each row');
      end before each row;
      after each row is
      begin
          ctr := ctr + 1;
          dbms_output.put_line('In after each row. booking_id='||:new.booking_id);
          coll_bookings_hist(ctr).booking_id := :new.booking_id;
          coll_bookings_hist(ctr).mod_dt := sysdate;
          coll_bookings_hist(ctr).mod_user := user;
          coll_bookings_hist(ctr).old_booking_dt := :old.booking_dt;
          coll_bookings_hist(ctr).new_booking_dt := :new.booking_dt;
      end after each row;
      after statement is
      begin
          dbms_output.put_line('In after statement');
          forall counter in 1..coll_bookings_hist.count()
              insert into bookings_hist
              values coll_bookings_hist(counter);
      end after statement;
      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, I placed dbms_output statements at various points to show how each section executes along what points. I 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 I 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_BOOKI NEW_BOOKI
   100 27-SEP-07 ARUP                           28-AUG-07 27-SEP-07
       101 27-SEP-07 ARUP                           06-AUG-07 27-SEP-07
       102 27-SEP-07 ARUP                           04-SEP-07 27-SEP-07
       103 27-SEP-07 ARUP                           15-JUN-07 27-SEP-07

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.