On Fri, 2010-08-06 at 09:39 +0800, Boxuan Zhai wrote:
> Besides, (I mean no offense, but) can this method really avoid losing
> row?
Not as you just specified, no.
You need *both* actions of RAISE ERROR and DO NOTHING, or you may as
well have neither.
(1) Natural style allows missing rows if you are not careful - and also
allows missing rows in future when COL is allowed to take value 'C',
which may not have been originally considered when SQL first written
WHEN NOT MATCHED AND COL = 'A' INSERT...
WHEN NOT MATCHED AND COL = 'B' INSERT...
(2) Shows code style required to explicitly avoid missing rows
WHEN NOT MATCHED AND COL = 'A' INSERT...
WHEN NOT MATCHED AND COL = 'B' INSERT...
WHEN NOT MATCHED RAISE ERROR
(3) More complex example, with explicit DO NOTHING, showing how it can
provide well structured code
WHEN NOT MATCHED AND COL = 'A' DO NOTHING
WHEN NOT MATCHED AND COL = 'B' INSERT...
WHEN NOT MATCHED RAISE ERROR
So DO NOTHING is the default and implies silently ignoring rows. RAISE
ERROR is the opposite.
Coding for those seems very easy, its just a question of "should we do
it?". DB2 has it; SQL:2008 does not. But then SQL:2008 followed the DB2
introduction of AND clauses, and SQL:2011 has so far followed the DB2
introduction of DELETE action also.
Given that Peter is now attending SQL Standards meetings, I would
suggest we leave out my suggestion above, for now. We have time to raise
this at standards meetings and influence the outcome and then follow
later.
There is a workaround:
WHEN NOT MATCHED AND COL = 'A' DO NOTHING
WHEN NOT MATCHED AND COL = 'B' INSERT...
WHEN NOT MATCHED AND TRUE INSERT INTO ERROR_TABLE (errortext);
where ERROR_TABLE has an INSERT trigger which throws an ERROR with given
text.
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.
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services