Can the expression with the conflicting row be normalized? i.e. is there a way for an expression to be applied to the conflicting row such that it will evaluate to the same value as the current row. One you have this you can place a UNIQUE KEY on the column of the generated expression. Once you have this, you don't need a trigger, just catching the error. On Fri, 3 May 2024 at 19:15, Alex Hermann via discuss <discuss@lists.mariadb.org> wrote:
Hi,
Is there a (built-in) way to detect from within a BEFORE INSERT trigger whether it was triggered by an INSERT or REPLACE statement?
I could surround every REPLACE statement with setting and clearing a variable. Problem is that this could easily be forgotten as there are multiple applications accessing the table. I'd rather not wrap access in a stored procedure or worse.
What will not work is to do a SELECT (tailored to every UNIQUE key) to determine if a record will be replaced. I do not need to know if a record _will_ be replaced, just that the intention is that it _can_ be replaced.
Background: the trigger does a validation that is not enforcable via a UNIQUE key. This precludes an INSERT ... ON DUPLICATE KEY UPDATE ... construction. When the validation finds a conflicting row, the query must fail when the _intent_ is to add a new record (INSERT), but succeed when the _intent_ is to replace (REPLACE) that row. -- Regards,
Alex Hermann
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org