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