The hidden side effects of the MERGE statement

The MERGE statement in Oracle is proving to be a very useful command, particularly if you have to perform a series of INSERTs and UPDATEs based on a fairly complicated or long winded piece of SQL. Oracle 10g also introduced a means of performing a DELETE. But are you aware of some of the side effects...

If you are merging into a table that has statement level triggers, then both the INSERT and UPDATE triggers will fire. If the merge statement also caters for a DELETE, then the DELETE trigger will fire too. If you have a single trigger for all operations combined, then it will fire the trigger for each operation covered. So even if your MERGE statement only ends up doing one operation, the trigger will still fire for all possible operations.

The DELETE operation is not an option in its own right. It is an alternative outcome for an UPDATE, i.e. if a particular condition is met, then perform a DELETE instead of an UPDATE. If you don't want to perform an update (e.g. your MERGE only performs INSERTs or DELETEs), then you must specifiy a dummy update in the SET clause, which must be based on a non-primary key column (or matching column for the MERGE). The resultant effect of this is that the ROW LEVEL UPDATE trigger always fires even for DELETEs.

So the MERGE statement can cause an awful lot of unnecessary trigger firing. A nice to have for statement level triggers is the ability to support a fourth operation for a MERGE or some other generic operation - which is affectively saying "I don't care what operation the statement is performing, just run this trigger code once for the statement".

A another nice to have with the MERGE statement is knowing how many rows were affected by each operation. The SQL%ROWCOUNT cursor attribute will not distinguish between the operations and only reports a total number of rows. So why not have a SQL%UPATECOUNT, SQL%DELETECOUNT, etc. This could be further extended when used with the FORALL in PL/SQL with the psueo array SQL%BULK_ROWCOUNT(index).

Here we go...

Well, I have finally fallen into line and eventually started my own blog. Actually it is under my trading name of Database Innovations, so this is not a personal blog. My main aim is not to bore you with my personal life ... "I got up this morning and burnt my toast whilst I read the latest revelations in the Oracle Magazine ...".

In fact, I'm not too bothered whether anyone reads this blog, because the main purpose of it to record snippets of technical Oracle and PL/SQL information and use it as an online scribblings notebook. I have an awful memory, so the only way I can rember all these the new things I learn day to day, week to week is to jot them down, so that I can reference them again. So its for me, but feel free to benefit it from it.

I will be reserving it for only small jottings on the more obscure things about Oracle which need real digging about in expert authors books, the numerous Oracle specialist websites, Metalink, my work colleagues, and of course from my own painful experiences in trying to figure things out, solve problems and work arounds in the mystical world of Oracle.

When I get the time, I'll probably comile them into articles, and publish them on my website www.db-innovations.co.uk . I have already written a limited number of articles and open source PL/SQL software which you can find on my site.

So, here we go ...