Thursday, February 14, 2013

Oracle PL/SQL 11g New Features

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.
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, 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:
     ---------- ---------     ------------------------------                --------      ---------
       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

Ordered Execution in Triggers

Since Oracle8, you have had the ability to define multiple triggers of the same type on one table—e.g., two triggers both after each row on insert on the same table. The type of triggers determine ordering of the execution: before statement, before row, after statement, and after row. However, if you have two after-row triggers—T1 and T2—which one will fire first?
The execution of triggers of the same type is somewhat random or at least not guaranteed to follow a pattern. Does that pose a problem? Let's see an example of a table called PAYMENTS, as shown below:
Name                                      Null?    Type
 -----------------------------------------                    --------     ----------------------------
 PAY_ID                                             NUMBER(10)
 CREDIT_CARD_NO                                     VARCHAR2(16)
 AMOUNT                                             NUMBER(13,2)
 PAY_MODE                                           VARCHAR2(1)
 RISK_RATING                                        VARCHAR2(6)
 FOLLOW_UP                                          VARCHAR2(1)

There is a need to calculate the risk rating from the type of the payments and the amount and store that in the column RISK_RATING. The following simple before update row trigger does the job pretty well:
create or replace trigger tr_pay_risk_rating
before update
on payments
for each row
        dbms_output.put_line ('This is tr_pay_risk_rating');
        if (:new.amount) < 1000 then
                :new.risk_rating := 'LOW';
        elsif (:new.amount < 10000) then
                if (:new.pay_mode ='K') then
                        :new.risk_rating := 'MEDIUM';
                        :new.risk_rating := 'HIGH';
                end if;
                :new.risk_rating := 'HIGH';
        end if;

Now, say someone adds another requirement: some items based on the column RISK_RATING, PAY_MODE, etc. should be flagged for follow-up in a new column called FOLLOW_UP. You could have modified the above trigger but it's always a good policy to leave existing code intact and create a new trigger of the same type (before update row) as shown below. (I have placed the dbms_output statements in the code to show how the triggers are getting fired.)
create or replace trigger tr_pay_follow_up
before update
on payments
for each row
        dbms_output.put_line ('This is tr_pay_follow_up');
        if (
                (:new.risk_rating = 'HIGH' and :new.pay_mode = 'C')
                or (:new.risk_rating = 'MEDIUM' and :new.pay_mode = 'K')
                or (substr(:new.credit_card_no,1,5) = '23456')
        ) then
                :new.follow_up := 'Y';
                :new.follow_up := 'N';
        end if;


Now, if you update the table:
SQL> get upd_pay
  1  update payments set
  2     credit_card_no = '1234567890123456',
  3     amount = 100000,
  4*    pay_mode = 'K'

SQL> @upd_pay
This is tr_pay_follow_up

This is tr_pay_risk_rating
1 row updated.

SQL> select * from payments;
     ---------- ----------------              ---------- - ------    -
         1 1234567890123456     100000 C HIGH   N

What happened here? The column risk_rating is HIGH and the column pay_mode is "C", which means the column FOLLOW_UP should have been "Y" but it's "N". Why? To answer the question, look at the order how the triggers fired: tr_pay_follow_up fired before tr_pay_risk_rating. The latter sets the column value as high risk. So when the former fired, it found a null (or "N") in the risk_rating column and therefore it considers the condition satisfied.
In this case the order of execution of triggers is very important. If tr_pay_risk_rating does not fire before the other one, correct variables will not be set and the set up will fail to implement the needs properly. The only possible way earlier was to place all this logic in one code and force the execution by ordering them in the code.
In Oracle Database 11g, you can place a clause in the trigger creation script that forces the ordering among triggers. Here is the upper portion of the trigger with the clause:
create or replace trigger tr_pay_follow_up
before update
on payments
for each row    
follows tr_pay_risk_rating
... and so on ...
 This clause (FOLLOWS <triggerName>) forces the trigger to fire after that specified trigger. You can test it by running the update script you saw earlier.
SQL> @upd_pay
This is tr_pay_risk_rating
This is tr_pay_follow_up
1 row updated.
SQL> select * from payments;
     ---------- ----------------              ---------- - ------    -
         1 1234567890123456     100000 C HIGH   Y
1 row selected.

The column was properly populated, as expected. Also note the correct ordering of the triggers that confirms what you intended to do.
Ordering of triggers allow you to reap the benefits of the modular code while making sure they get executed in the proper sequence.

Straight Sequences

When you had to use a sequence in a PL/SQL program earlier, you had to use a construct like SELECT <Seq>.NEXTVAL INTO <VariableName> FROM DUAL prior to this release.
        trans_id number(10);
        select myseq.nextval
        into trans_id
        from dual;

Not anymore. You can directly assign the next value of a sequence to a variable:
        trans_id number(10);
        trans_id := myseq.nextval;

Now, that's what I call simplicity.

No comments:

Post a Comment