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

Would it matter that a Mercedes car has beautifully formed body panels, polished to perfection, designed to be pleasing and visaually enjoyed, or could it just be beaten out of old corrugated metal and welded together - after all it is only a means of getting from A to B. And after all, code is only for executing not for visual enjoyment, isn’t it? I wouldn’t agree.

I have now resolved to the fact that code resembling hand beatten corrugated metal is going to be with us for a while, or until the concept of form in an artistic sense is better appreciated, and to bang on about, as I usually do, seems to be turning into a fruitless one man battle. Today’s programmer’s are tomorrow’s designers and architects, so they will need show some creativity if we don’t want the industry to be dumbed down into oblivian, and starting with the visual aspects of their code is a good starting point.