On vrijdag 3 mei 2024 13:25:10 CEST Jaco Kroon via discuss wrote:
On 2024/05/03 12:49, Alex Hermann via discuss wrote:
On vrijdag 3 mei 2024 12:27:49 CEST Jaco Kroon via discuss wrote:
This is ~ what I understood. I can't find a way to determine the {IS_INSERT} above, however (from http://url7640.wenlex.nl/ls/click?upn=u001.MdwzzSnEbdLo8Gnan-2FT5Ljt0NIym6Wi...
"If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, and it is an AFTER trigger, the trigger will not run, but the original statement will."
So under the assumption that you're using transactional tables here ... is it possible to verify AFTER INSERT? In other words, instead of testing if the statement will violate the constraints, rather check afterwards if the constraints has been violated, and if so, roll back.
Interesting idea, I'll have to dive into that. Though my first gut feeling says it will have the same problem: Was the row inserted or replaced?
Probably not helpful, so sorry if I'm just causing noise, I find this an extremely interesting problem.
Suggestions are always welcome. What I forgot to mention is that a REPLACE may only succeed if the new data is not in cnflict with _other_ rows. I.e. either no conflict at all OR only in conflict with the row it replaces. An INSERT must fail on any conflict. Writing this down does trigger an idea which would even make INSERT .. ON DUPLCIATE KEY UPDATE .. possible, but I doubt I haven't already tried that. -- Alex Hermann