Triggers Considered Harmful, Considered Harmful

Where TK agrees with TK (or: why are triggers harmful)

2012-02-06T11:27:00.000-08:00

Sofar we've explored playing around with a few triggers to implement a business rule. In this post I'd like to step back a bit and take a birds-eye view at the common use-cases that I see brought up for introducing triggers into the app-development picture.

The first use-case is: we use triggers to assign or modify column values of mutating rows (that is, rows currently being inserted, updated, or deleted). Here's a few typical examples of this use case that, I'm sure, must look familiar to you too.

For this use-case we always use a row-level trigger (for each row), as this type of trigger allows us to manipulate :new.[column-name] variables. We can re-assign supplied (by the trigger DML-statement) column values, or initialize ones that were not supplied. So for instance the use enters all column-values but the Created and By_User columns, which are initialized by a row-trigger we wrote.

The second use-case is: we use triggers to execute insert/update/delete statements. So we perform some DML against a table. This table has some trigger, in which we perform more DML.

Of course this can lead to a cascading effect: the DML that we perform from within our trigger body might be against a table that too has triggers. In which there might be more DML-statements performed, and so on and so forth. Typical examples of why we perform DML-statements inside triggers, are: we want to audit changes against the table, or we want to maintain redundant data stored elsewhere in the database design.

The third use-case is: we use triggers to execute non-transactional stuff. And by "non-transactional" I mean stuff that, once executed inside our transaction, does not participate in a rollback, in case we (the code-path) decides to perform one.

The most beautiful example of the last decade for this is, we call a web-service inside our trigger body. In case this is a web-service that actually 'does something' (changes/transacts information at/behind the endpoint of the service), then obviously when we rollback our triggering DML-statement, whatever was changed by the web-service, remains changed. Other examples of this use-case are: we send email from our trigger body, or perform file I/O. We can also perform an autonomous transaction (one that actually performs some DML which given it's executed inside the autonomous TX, cannot be rolled back by the parent transaction). This is actually a blend of this use-case with the previous one.

So what's harmful about all these use-cases? Well, they make stuff happen automagically (copyright the other TK).

What we mean by this is, that instead of only getting the effect of the triggering DML-statement, you also get the effect of whatever is being performed inside the trigger body. And exactly that is the harmful bit.

In a SQL DBMS, the insert, update and delete 'operators', constitute three primitive operators that we can use to manipulate the data held inside the database (tables). Primitive here is used in the sense of primary or basic. Sort of like Inc(rement) and Dec(rement) are primitives that can be used in machine language to increase or decrease a register's value. These primitive operators have well understood and universally known change-semantics. Say you start with an empty EMP table, you then insert a employee row, you will end up with a change of the EMP table: it now has a single row in it. And all other bits and pieces of the database remain unchanged.

Triggers executing automagic stuff change these universally well-understood change-semantics of our three primitive operators.

And this is harmful: because nobody (after you've gone) expects insert, update or delete to quietly behave differently. For those of you who have done some machine programming in a distant past: it's like increasing one of your registers, and suddenly (as an automagic side-effect) the top of the execution stack is increased also and has some subroutine's address pushed on it. Not something you'd expect...

The TCO (total cost of ownership) of an application sits not in initially building the thing. It sits in the years, sometimes decades, the thing lives. And you, but more probably others, maintain it. And more often than not these developers expect their available primitive operators to follow the primitive change-semantics.

I'll finish this post with the following thought:

If you agree on the three use-cases discussed being harmful, do you then also agree with me that:

The cascade delete option of a foreign key, and
The default clause of a table column, and
The char (fixed-length) datatype

are to be considered harmful too.

Aren't they conceptually about the exact same problem? Introducing different change-semantics to our three primitive operators?

I'd be interested to hear of any other 'features' that change the way insert, update or delete behave.

Statement level constraint consistency

2012-01-29T08:13:00.000-08:00

In the past week I've been investigating how Oracle fires triggers with the Merge and Multi-Table-Insert statements. Also took a look at 'statement-level constraint consistency' with these two types of statements. My findings are here: http://rulegen.blogspot.com/2012/01/statement-level-constraint-validation.html

Normal transmission on harmful triggers should resume shortly.

Workaround 2: don't use the row-trigger

2012-01-04T06:12:00.000-08:00

So where were we?

We wanted to implement the following business rule: "Cannot have a manager without a clerk in the same department." At the end of this post, we tried implementing that using a straight-forward row-trigger, which introduced us to the mutating table error. And we've explained thereafter (here and here) that this error is there for a very good reason.

We've also clarified that the two common 'workarounds' 1a and 1b really should not be used.

So using a row-trigger to check for a clerk whenever a manager is inserted will not work. Remember the reason we wanted to use a row-trigger is because in it we have available :new.deptno which represents the department into which a manager just got inserted. This enabled us to efficiently check only that department for the necessary clerk.

So what about a statement trigger then? Obviously it forces us to validate all departments, since within the context of the after insert statement trigger, we do not know anything about the actual insert(s), other than that some insert took place.

Above is an attempt to implement our rule using the after insert statement trigger. As you can see we validate all departments. This is not a very efficient solution to implement our business rule. Not only do we validate all departments, we also validate them on all types of inserts. Obviously when we insert, for instance, a trainer, there is no need at all to validate this rule (assuming the rule was fine, before this insert), since inserting a trainer cannot violate this rule: such an insert doesn't 'touch' the rule.

We will explain in detail how to a) workaround the mutating table issue + b) get an efficient (and correct) implementation for our rule using a statement trigger in multiple future posts. But before we do that, we'd like to spend some time on common use-cases of triggers, and why they gave rise to the general consensus out there of triggers being harmful.

Look mom: a mutating table error without a trigger!

2011-12-30T01:10:00.000-08:00

Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?

ORA-04091: table ... is mutating, trigger/function may not see it

We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from coding logic that might work differently tomorrow than from what it did today.

Let's quickly show you this scenario. We create the EMP table again and insert some test data into it.

drop table EMP;
create table EMP
(EMPNO number(3,0) not null primary key
,ENAME varchar2(20) not null
,SAL number(4,0) not null)
/
insert into emp(empno,ename,sal) values(100,'Toon',4000);
insert into emp(empno,ename,sal) values(101,'Izaak',5000);
insert into emp(empno,ename,sal) values(102,'Marcel',7000);
insert into emp(empno,ename,sal) values(103,'Rene',8000);
commit;

Our HR department decided that above salaries should be aligned a bit more in the year 2012. And in their wisdom they decided that the following update should be run at the end of the year:

update EMP e1 set e1.SAL =
e1.SAL + ((select avg(e2.SAL) from EMP e2) - e1.SAL)/2
/

This update brings everybody's salary a bit more towards the current average salary of all employees. The dba does a testrun of the script in the acceptance instance:

It runs well, HR confirms that it does what it was intended to do, and dba is fine with this in production.

Here come the QA department though: they need to 'ok' the running of this script in production too. So they inspect the script with all QC guidelines in mind. Result of their review is that they won't allow this. There is distinct 'business logic' in there that needs to be removed from the statement and implemented in a stored function, so that it 'can be reused sometime in the future' (I've seen this happen in real companies...). They mandate that a function be implemented such that the update statement will be of the following form:

update EMP e set e.SAL = f_new_sal(e.SAL)
/

So the developers pump out the following function for this:

create or replace function f_new_sal
(p_current_sal in number) return number as
--
pl_avg_sal number;
--
begin
--
select avg(SAL) into pl_avg_sal
from EMP;
--
return p_current_sal + (pl_avg_sal - p_current_sal)/2;
--
end;
/

Everybody is happy. The dba executes the testrun again:

WTF? Oracle throws a mutating table error? But there's no trigger involved here...

Why is this happening?

If you understood the explanation of this error in my previous posts sofar, you should be able to figure this one out. Oracle again detects that we seem to be selecting from a table that's currently mutating. Think about what is happening here:

The update starts identifying the rows that need their SAL column value changed;
It does this in some undetermined order (!);
When the first rows is found, it invokes our function to determine the new SAL column value for it;
Our function queries the EMP table, which is currently mutating: bingo we hit the error.

When Oracle steps out of the SQL-engine that's currently executing the update statement, and invokes the function, then this function -- just like an after row update trigger would -- sees the intermediate states of EMP as they exist during execution of the update statement. This implies that the return value of our function invocations heavily depend on the order in which the rows happen to be updated.

Wanna see this happening? Let's use our database link "workaround" to prevent the mutating error. We create a loopback database link and change our function accordingly:

create or replace function f_new_sal

(p_current_sal in number) return number as

pl_avg_sal number;

begin

select avg(SAL) into pl_avg_sal

from EMP@loopback; -- Here: added db-link.

return p_current_sal + (pl_avg_sal - p_current_sal)/2;

end;

Dba can now run the update without hitting the error:

But look at the SAL values, only Toon's salary is updated correctly. These are not accepted by HR...

And these values will be different if we force a different order of updating the rows, for instance like this:

update (select * from EMP order by EMPNO desc) e

set e.SAL = f_new_sal(e.SAL)

The proof is in eating the pudding:

Now Rene's salary is the only one being updated correctly.

So again, don't forget: ORA-04091 is your friend.

Note that in the original version of the update statement (without the function), Oracle ensures that the repeated 'invocations' of the subquery (select AVG(e2.SAL) etc.) all see the same snapshot of the database: the one that existed at the start of the update statement. So there is no indeterministic behavior in that case.

A fix for the issue demonstrated could be to just add the autonomous transaction pragma to the function. We would then get the same (deterministic) behavior as the original version of the update statement.

A better fix in this case though would be to not have the function at all, and just go with the original update statement.

The mutating table error prevents non-deterministic behavior of your code

2011-12-23T01:13:00.000-08:00

A short recap of workarounds 1a and 1b from our previous post.

In workaround 1a we ensured that the query that was selecting from the mutating table, was being executed from within an autonomous transaction.
In workaround 1b we ensured that this query was being executed via a loopback database link.

We also discussed the major difference between the two workarounds:

1a: the query sees the mutating table as it existed prior to the start of the main transaction.
1b: the query sees the mutating table as it is being mutated by the triggering DML statement. Essentially this workaround behaves as if Oracle did not have a mutating table error at all.

Please never implement these workarounds. Especially 1b for the reason I will demonstrate in this post. And do you really want the overhead of going through a database link every time the row-trigger fires? And potentially have twice the number of database sessions in your database instance? I hope not.

Apart from this, you will have planted a booby trap in your application code: your code can now behave non-deterministically.

To discuss this we will investigate how our row-trigger behaves not in the case of a single row insert, but a multi-row insert. Given our business rule, no manager can be without a clerk in the same department, a multi-row insert that inserts both the manager as well as the necessary clerk as the first two employees for a new department in one go, is a valid insert statement that our trigger-based rule-enforcement code should allow.

Let's look at the behavior of our row-trigger using workaround 1b (loopback db-link) first. Suppose we have a table called EMP_STAGE. This (staging) table is holding employee-rows that are to be inserted into the real EMP table. Table EMP_STAGE currently holds two employees: a manager and a clerk both working in department 42.

Now see what our row-trigger does.

Our row-trigger does not allow this insert. Why? Well think about it. How is the statement 'insert into EMP select * from EMP_STAGE' executed? Particularly in what order are the selected from EMP_STAGE and then inserted into EMP? Apparently in the case above the manager was the first one that was selected and inserted into EMP. Which would then fire our row-trigger, which would then see the mutating table (via the loopback db-link) and not find the required clerk in department 24. Hence our row-trigger raised the business rule error. And the multi-row insert statement never reaches inserting the second row (the clerk), it is cancelled by the raise_application_error and rolled back.

But what if the rows happened to be processed in a different order? We can force this by simply adding an order-by clause to the subquery in the insert statement (we re-execute the insert and have added 'order by empno desc'):

Our two-row insert statement now executes succesfully!

So what happened in this case? Due to the order by clause we've ensured that employee 111 (the clerk) got inserted first. The row-trigger fires, sees it's not a manager and therefore nothing needs to be done. Next employee 110 got inserted. The row-trigger fires again, sees it's a manager, and executes the business rule check by calling our procedure. This procedure now queries the EMP table (via the loopback db-link) and it actually sees (!) the clerk that just got inserted by the same statement, and therefore executes successfully.

Now the order in which rows get inserted by a multi-row insert statement is (and rightfully should be) of no concern to you. Think of the subquery instead of being a simple 'select * from emp_stage', being a complex multi-table join: depending on current contents/sizes of the tables involved, statistics might differ which in turn might cause different execution plans for the insert statement, which in turn might change the order in which rows get inserted by a multi-row insert statement. If your code is not immune to this order then in effect your code executes in a non-deterministic fashion. And this is the essence of why ORA-04091 is your friend. It protects you against developing software that might work today, and not work tomorrow.

So how does this all work with the autonomous transaction workaround? Here's the same two-row insert for that scenario, executed without and with the forced order (EMP again has no employees in department 42).

As you can see both versions of the insert statements fail with this workaround. The explanation for this is simple. Since the query that checks for the necessary clerk is run from inside an autonomous transaction, all it sees is the EMP table as it existed prior to start of the insert statement: in which there were no employees for department 42. So in this case the 'workaround' is not a workaround at all: yes it prevents the mutating table error, but no it does not allow a valid insert-statement to execute successfully. Note that it even wouldn't allow two subsequent single row-inserts: first insert a clerk, then insert a manager. With this workaround you are forced to commit the clerk first, and only then insert the manager.

So, in summary:

Again, forget about these two red herrings: don't use them.

In the following posts we will start developing how to correctly work around the mutating table error. And as many of you probably know, it involves a combination of row and statement triggers.

In the meantime happy holidays!

"Workarounds" for ORA-04091

2011-12-13T22:35:00.000-08:00

In the previous post, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table.

The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger);
It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager;
The procedure then queries the EMP table to see if a Clerk exists in this department;

If it finds no such Clerk, the procedure raises an application error, which causes the after-insert-row trigger to fail. This in turn prevents the insert of the Manager, since it would violate our business rule (the Manager insert will undergo a statement-level rollback due to the row-trigger failing);
If it finds such a Clerk, then the procedure finishes successfully, which causes the after-insert-row trigger to finish successfully, which causes the Manager insert to execute successfully.

A straight-forward 'use-case' of using a (row) trigger, right? Of course this setup didn't work, since the cursor in the stored procedure will always hit the mutating table error, thus preventing a Manager insert even if the necessary Clerk would indeed be present.

So, what's the workaround for this? Well there are a two I would like to discuss in this post: one of them is very 'popular', the other one is -- I guess -- not so well-known.

I cannot stress enough upfront right now, that both 'workarounds' are red herrings.

Workaround 1a: use the autonomous_transaction pragma inside the stored procedure.

If we add just one line at the top of the stored procedure, we can fix this baby (see red-arrow line below).

By ensuring that all code inside the P_CHECK_FOR_CLERK procedure will run as an autonomous transaction, we can prevent the mutating table error. Why? Because it causes Oracle to 'step out' of the current transaction context, in which EMP is currently 'mutating', and open a new transaction in which EMP is not mutating, and therefor can be queried without hitting the mutating table error. So all we need to do is add the PRAGMA line highlighted above. We do not need to add an explicit COMMIT (or ROLLBACK) at the end of the procedure, which is usually required to end an autonomous transaction, since the procedure doesn't really start a transaction at all: is just issues a query.

This is the popular workaround. Now for the lesser known workaround.

Workaround 1b: use a loopback db-link to query the mutating table.

By querying the EMP table to look for a Clerk, via a loopback database link, we can fix this baby too.

So we create a loopback database link (which we call loopback above) which connects back to our application owner schema. We then add the text '@loopback' to the table name (EMP) inside the FROM-clause of our query that was hitting the mutating table (second red arrow above). Done. We'll demonstrate in a minute that this 'fixes' our issue as well.

But before we do that you really need to understand a fundamental difference between these two workarounds.

In the preliminaries that we discussed in the previous post, one of the observations that was made about an autonomous transaction was that all code in such transaction cannot see any (uncommitted) changes made by the other (parent) transaction. So what this means for our example is that the cursor in the stored procedure (the one that tries to find a Clerk) which is executed by an 'after-row' trigger, will see the EMP table without the Manager that was inserted by our triggering insert statement.

Yet.

The cursor that uses the loopback database link, which is executed from within a new session created by the database link, *will* see the mutating table as it exists at that point-in-time in the transaction of the 'original' session (the one that issued the Manager insert). Why? Because Oracle detects that both sessions are now part of a distributed transaction, and the database link session will see the same database snapshot as the other session. Now this is pretty radical, I think. When I discovered this behavior I was stunned for a few moments: here we have a database session (OK, it's one that was created by a database link, but still) a session that is able to see uncommitted changes of a different session! I will show you a demo that proves this behavior in a minute.

Now wouldn't that be an in-depth (trick) question for some fancy certification exam:

"When can a database session see uncommitted changes of another session?"

a) Never.

b) Always, this is default behavior.

c) If it's using an autonomous transaction.

d) If the session was opened by a database link.

;-)

Now let's test both workarounds. Remember we are trying to insert a manager into department 42, in which no other employee works yet.

Et voilá: no more mutating table error. Our row-trigger + procedure work, they give us the business rule error.

So what's wrong with both approaches? Can you guess it? I'll give you a hint: think about multi-row inserts. Will they work given workaround 1a (the autonomous transaction) or workaround 1b (the db-link trick)?

I'll discuss this in the sequel, which should follow promptly.

Some preliminaries

2011-11-25T13:13:00.001-08:00

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

So what triggers are we talking about?

2011-11-22T07:26:00.001-08:00

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

2011-11-22T00:58:00.001-08:00

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:

From http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
"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: http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
[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 asktom.oracle.com. 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.