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