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

Tuesday, November 22, 2011

So what triggers are we talking about?

Here's an overview of the talk.

This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to achieve the end.

The most common types of triggers, the ones everybody probably used at some time in their pl/sql programming career, are the "DML event" triggers. As above slide shows, there are twelve of such triggers: four each for every type of DML statement, Insert, Update and Delete. These triggers will be fired by the DBMS before a DML statement, after a DML statement, and before/after each affected row of the triggering DML statement. Stuff you all know right? The big difference between the statement-level and the row-level triggers, is that the latter ones can inspect (and change) the column-values of the current row that is being inserted/deleted/updated.

So here's an example. Suppose we have an EMP table that holds employees, and we want to execute an update statement that will increase the salary of all clerks (see update statement above). This will for the given table affect three rows. The before update statement trigger will then fire once. Next for each affected row the before and after row triggers will fire. And finally the after statement trigger will fire.

So if we create the four update triggers on the EMP table as follows:

We will get the following output (given we have set serveroutput to on).

Nothing new so far, I hope. Before we continue I just wanted to mention that as of Oracle11G we have the compound trigger feature.

 A compound trigger enables us to create the four update triggers above all in one go as follows:

Now, do you know why Oracle introduced compound triggers? I'll talk about that in a later post. What I'll say now is this: compound triggers are the answer of an enhancement request made by you (the pl/sql community) a long time ago. Because you have always hit a certain programming pattern with regards to triggers, when using them for a certain use case. Again I'll explain this in more detail in a future post.

So these are the triggers that are in-scope of this blog: DML event triggers, be them created individually or four in one go using the compound trigger mechanism.

Oracle DBMS offers us with many more triggers:

All of which will not be the matter of subject for this blog.

Stay tuned.

Starting this blog

So I finally was able to find some time to start this blog. I mentioned before that I might do this in the asktom discussion here. This blog will be about database triggers. Oracle database triggers that is. I'm a strong advocate of database triggers. Now hold your horses... I have been using database triggers ever since Oracle7 in the early nineties brought them to us. But I've been using them for a very specific reason, and have been presenting about this ever since. I will blog about this in a very similar way as I blogged about TheHelsinkiDeclaration, by documenting the presentation I have frequently given now at various Oracle related seminars and user group meetings.

Let me present exhibit A, showing that I have been presenting about a particular use-case for using database triggers for quite some time now. Here's the front cover of the 1994 International Oracle User Week (that's how Oracle OpenWorld used to be called in the nineties) conference.

And here's a part of the contents.

Paper #10, "Business Rules: Classification and Implementation (...)". And as you can see I was employed by Oracle back then. Note that Steve (Feuerstein) was already presenting in those days too. Now some of you can already guess what my talk was about. The use-case for triggers that I'm advocating, that is. For those of you who don't, stay tuned, I will explain the use-case and my arguments for it, in the posts that will follow.

So that was (almost) two centuries ago, business rules presentations by T(oon) K(oppelaars)...

Fast forward to this millenium. Here's another TK on triggers in the database:

"Triggers are so abused and so used inappropriately, I'd rather live without them."
"There are no times triggers cannot be avoided. They are purely a convenience that is overused, abused, and improperly used."

And from:
[on why he doesn't like triggers] "Because I hate being surprised or tricked. And triggers are all about trickery and surprises."

The other "TK" of course is Tom Kyte from Since you are reading this blog, I"m sure Tom Kyte needs no further introduction. He makes no secret of the fact that he has strong reservations about the database feature called "triggers". And this opinion is wholeheartedly embraced by the PL/SQL community at large. The default response whenever a question on database triggers pops up on  PL/SQL OTN forum is:

"Triggers should be avoided as much as possible."
"Don't use them, they are bad."
"Triggers are considered harmful."

Well excuse me, but I find this general consensus harmful.

At this time I should disclose that I run a company called RuleGen, which is also the name of a product that generates Oracle trigger code to implement business rules.

A year ago I decided to present on why I find this general consensus harmful. And this resulted in a presentation at Hotsos 2011. The title of this presentation is: "Triggers considered harmful, considered harmful". And that's not a typo, no. I'm using the "considered harmful" concept which was introduced by Edsger Dijkstra when he published a paper called "Goto statement considered harmful". And as you can read here, it gave cause to quite some 'follow up' papers with the same "typo" in their titles.

Recently I also delivered this presentation at the BGOUG. And it has been submitted for presentation at the upcoming ODTUG. I'll let you know if it got accepted, so you can attend ODTUG and hear a live version of this blog.

So much for the introduction. Future posts we will go into the details of why I find triggers not harmful at all given they are used for a very specific use-case.