On Fri, Aug 6, 2010 at 3:41 PM, Simon Riggs
<simon@2ndquadrant.com> wrote:
On Fri, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote:
> > SQL:2011 makes no mention of how MERGE should react to statement level
> > triggers. MERGE is not a trigger action even. Given considerable
> > confusion in this area, IMHO we should just say the MERGE does not call
> > statement triggers at all, of any kind.
>
> IMO the UPDATE/DELETE/INSERT actions should fire the respective
> statement level triggers, but the MERGE itself should not.
When, and how?
If an UPDATE is mentioned 5 times, do we call the trigger 5 times?
My current process for BEFOR / AFTER STATEMENT trigger on MERGE is to fire the triggers for all action types that appears in the command, unless it is replaced by a INSTEAD rule. But the triggers for one action type will be fired only once. That means you will get both UPDATE and INSERT triggers be activated for only once if you are executing a MERGE command with 5 UPDATEs and 10 INSERTs.
What happens if none of the UPDATEs are ever executed?
The triggers (I mean triggers for statement) will be fired anyway even the UPDATE action matches no tuple. This is not for MERGE only. If you update a table with the command
UPDATE foo SET ... WHERE false;
It will also fire the STATEMENT triggers of UPDATE type on foo (I think so).
And, even not been asked, I want to say that, in current implementation of MERGE, the row level triggers are fired by the actions that take the tuples. If one tuple is caught by an UPDATE action, then the UPDATE row trigger will be fired on this tuple. If it is handled by INSERT action, then the INSRET row triggers are on.
Hope you agree with my designs.
Best explain exactly what you mean.