Efficient PL/SQL Coding
A trigger that fires several times at different triggering events, ability to force triggers of the same type to follow a sequence, and the new CONTINUE statement are some of the new gems that make PL/SQL programming easier.
See Series TOC
Since its inception, PL/SQL has been the language of choice for programming in Oracle Database. Over a period of time, we have seen the language evolve into a comprehensive development platform by virtue of more and more functionality that requires less coding. Oracle Database 11g makes PL/SQL coding yet more efficient for programmers. In this installment, you will see some examples that offer an introductory glimpse into this new functionality.
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.
A trigger that fires several times at different triggering events, ability to force triggers of the same type to follow a sequence, and the new CONTINUE statement are some of the new gems that make PL/SQL programming easier.
See Series TOC
Since its inception, PL/SQL has been the language of choice for programming in Oracle Database. Over a period of time, we have seen the language evolve into a comprehensive development platform by virtue of more and more functionality that requires less coding. Oracle Database 11g makes PL/SQL coding yet more efficient for programmers. In this installment, you will see some examples that offer an introductory glimpse into this new functionality.
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.
No comments:
Post a Comment