Friday, November 25, 2011

Some preliminaries

There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.

If you try to do so, Oracle will throw an ORA-04092 at you. A common "workaround" of developers who really think that they need to do this kind of stuff from within trigger code, is to wrap the offending code inside an autonomous transaction, thereby not affecting, transactionally, the main transaction which is currently executing the DML statement (that caused the trigger to fire). There is an important observation to be made at this time: the SQL-code inside such autonomous code block, cannot see the changes that are being made by the DML statement that is executing. Nor can this SQL-code see the effect of any other DML statements that might have been executed earlier inside the main transaction. Oracle treats the autonomous code block as if it were executed inside a different session. And sessions never see changes made by other sessions that have not yet committed these changes.

Remember this one. I'll refer back to this observation in the next post when I discuss common "workarounds" for the infamous mutating table error.

The second remark:

If a trigger fails, due to an exception being raised (and left unhandled), then the triggering DML statement (and all that might have been done by the trigger code self), will be rolled back. Note that this is a statement level rollback: so any prior changes executed in the transaction, are still left "posted" (not rolled back). You can view this statement level rollback as a 'rollback to savepoint', where the savepoint was set (by Oracle) just prior to starting the DML statement execution.

Third (an final) remark in this post: row level triggers are not allowed to read what's called the mutating table.

If your row level trigger code queries the table that's being affected by the triggering DML statement, then Oracle will throw an ORA-04091 at you and cause the above mentioned statement level rollback. If you've ever played around with (row) triggers, you must have encountered this issue. I think my first encounter was in 1993. And there is a very clear cause why you will encounter this issue: the kind of stuff you would like to do in a row-trigger, often, no very often, requires you to query the mutating table. I'll come back to this later.

One of my goals is to explain to you that this error is not your enemy. No, it's your friend. It prevents you to develop software, that might work today, but no longer tomorrow. I'll demonstrate that in the next post. For now we'll close this post by quickly building an example that gives rise to the mutating table.

Suppose we need to implement the requirement that every employee whose job is Manager, must be accompanied by another employee whose job is Clerk and works in the same department (as the manager does). Well, we can implement that easily, all it requires is a row trigger, right?

We build an after insert row trigger on the EMP table, which will verify, whenever a Manager is inserted, that a Clerk exists in the same department. Here's the code for that procedure you see called in above trigger body:

And as you can see, this code queries the mutating table. So let's test this trigger. Here's the contents of our EMP table, and an insert statement that attempts to insert an Manager into department 42 (which has no Clerk).

And as expected, Oracle throws the mutating table at us.

In the next installment I'll discuss common "workarounds" for this issue. They really aren't workarounds, but everyone calls them that...


  1. Well ... Sceptical, but definitly looking forward to reading your posts. I've seen so much bad use of triggers, that I do get sceptical when someone says they're not harmful. Don't keep us waiting to long :-)

  2. Transaction should follow ACID principle in which D stands for durability , now lets say there are 10 triggers on a table only 5 were able to fire after commit and then instance crash happens will it not result in bad data and difficult to debug.
    What precaution oracle take to avoid it ?

  3. L,

    There are lots of use-cases where triggers are indeed harmful. I've seen similar bad uses, of course. There is one use-case however that fits triggers perfectly. The problem with this use-case is that it happens to be a difficult/complex one. But that to me is no reason not to use triggers for this particular use-case. During the course of posting on this blog I intend to explain all this.


  4. Atul,

    1) Your question/example seems to imply that the triggers 'do transactional stuff' (i.e. the insert, update and/or delete data). This is one of the harmful use-cases for triggers.
    2) I get the feeling that you think a commit occurs after the execution of each trigger? That's not how it works in Oracle. Above preliminaries should have explained that.


  5. Actually it can happen from Oracle Forms, meaning Post Commit triggers ….. but that is different issue all together.

  6. lets not restrict to oracle triggers , there are also triggers used by front end. So in general *Trigger* irrespective of if they are from oracle or front end are they good or bad ?

  7. Ton,

    "You can view this statement level rollback as a 'rollback to savepoint'"

    Very nice explanation confirmed here below:

    mhouri > create table t1 (x number, y number);

    Table created.

    mhouri > alter table t1 add constraint t1_y_ck check (y > 10);

    Table altered.

    mhouri > create or replace trigger t1_trg
    2 after update on t1
    3 for each row
    4 begin
    5 update t1
    6 set y = 10
    7 where
    8 x = :new.x
    9 ;
    10 end t1_trg;
    11 /

    Trigger created.

    mhouri > set serveroutput on
    mhouri > select * from t1;

    no rows selected

    mhouri > begin
    2 insert into t1 values (1,11);
    3 insert into t1 values (2,22);
    4 begin
    5 update t1
    6 set y = 2
    7 where x = 1;
    8 exception
    9 when others then
    10 dbms_output.put_line('update trigger raises exception');
    11 dbms_output.put_line('an implicit rollback to savepoint has been done');
    12 dbms_output.put_line('transaction has been rolled back up to the update instruction');
    13 end;
    14 -- if we commit here this means that the two inserts done before the
    15 -- implicit position of the savepoint (just before the update) will be
    16 -- posted to the data base
    17 commit;
    18 end;
    19 /

    update trigger raises exception
    an implicit rollback to savepoint has been done
    transaction has been rolled back up to the update instruction

    PL/SQL procedure successfully completed.

    mhouri > select * from t1;

    X Y
    ---------- ----------
    1 11
    2 22
    This is why one need to be very carreful about exception without a RAISE instruction

    Mohamed Houri

  8. Mohamed,

    Nice example to illustrate the rollback, yes.
    But you do realize that the T1_Y_CK constraint is redundant in this example: without the constraint your row trigger will simply hit the mutating table issue, and have Oracle raise the exception for that.

    You did illustrate the order in which Oracle executes the code: declarative constraint validation before after-update-row logic.