Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Trigger Enhancements in Oracle 11g Release 1

Prior to Oracle 11g Release 1, if you have two (or more) triggers on a table for same action – event (BEFORE, AFTER – INSERT, DELETE, UPDATE), there is no guarantee in which order there will be fired. Oracle database 11g Release 1 has introduced improvements in triggers.

On Order of Execution of Multiple Triggers

Prior to Oracle database 11g, developers can never assure you the order of execution of triggers. If you have more than one trigger set on the same triggering conditions, Oracle documentation says that, the order of execution will be random.

Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.

This was a hindrance in many scenarios. Most of the time PL/SQL developers would do away with this in simple manner. They will write one trigger and let it to call all other similar triggering actions. Although this doesn’t look like a good practice, it was at least better than to leave it for Oracle to decide at random.

CREATE TRIGGER with FOLLOWS CLAUSE in Oracle DB 11g

It was long expected, we need some way to tell the compiler about the order in which the triggers should be triggered. This long awaited enhancement was rolled into Oracle database 11g Release 1.

Consider the following table and triggers

create table t1
( id      number(6)
, value   varchar2(30)
);

SQL> desc t1
Name           Null?    Type
------------- -------- --------------------------------------------
ID                      NUMBER(6)
VALUE                   VARCHAR2(30) 

I have a requirement to perform two actions on same event (before insert, for each row). Sure I can put them into one trigger, but for code maintainability I want to create them separate. I will start like below.

create or replace trigger bi_id_t1_trg
before insert on t1
for each row
begin
dbms_output.put_line('Executing First trigger');
end;
/

The trade-off would be, I will never be sure of the order of execution of the triggers. With Oracle database 11g release 1, I can use the new clause ‘FOLLOWS’ in trigger definition to keep them definitive. This clause lets the compiler to know which trigger has to be executed after which. FOLLOWS clause is optional. Note that the first trigger doesn’t have this in trigger definition. When not mentioned and in-case of multiple trigger on same triggering action, Oracle assumes random order of execution.

create or replace trigger bi_id_t1_trg2
before insert on t1
for each row
follows bi_id_t1_trg
begin
dbms_output.put_line('Executing Second trigger');
end;
/

SQL> insert into t1
2  values (1, 'one')
3  /
Executing First trigger
Executing Second trigger

1 row created.

As you can see, the order of the execution of triggers are confirmed.

3 responses to “Trigger Enhancements in Oracle 11g Release 1

  1. Pingback: Compound Trigger – A Performance Improvement for Trigger Executions « Abstract Confusions

  2. Savannah Divin June 19, 2010 at 6:46 PM

    I stumbled upon this site from looking over Google and just wanted to say thank you for this interesting entry. Thanks again!

  3. Rod Oki September 6, 2010 at 12:56 AM

    There are certainly a couple more details to take into consideration, but thanks for giving this information 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: