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 ...
... 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
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
begin
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';
else
:new.risk_rating := 'HIGH';
end if;
else
:new.risk_rating := 'HIGH';
end if;
end;
/
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
begin
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';
else
:new.follow_up := 'N';
end if;
end;
/
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;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
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
begin
... 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;
PAY_ID CREDIT_CARD_NO AMOUNT P RISK_R F
---------- ---------------- ---------- - ------ -
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.
declare
trans_id number(10);
begin
select myseq.nextval
into trans_id
from dual;
end;
Not anymore. You can directly assign the next value of a sequence to a variable:
declare
trans_id number(10);
begin
trans_id := myseq.nextval;
end;
/
Now, that's what I call simplicity.
No comments:
Post a Comment