Hi Alex, On 2024/05/03 11:14, Alex Hermann via discuss 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.
May I assume that it's also not quite as trivial as doing something like this: CONSTRAINT `valid_phone` CHECK (`phoneno` regexp '^0[1-8][0-9]{8}$') Otherwise, if perhaps you could provide a little more clarification? A contrived example even, I'm inferring that you're not in a position to disclose the exact details here :). One other thought: does the trigger perhaps execute after the previous record was deleted? In this way by the time the trigger executes the old row was already deleted and the new row can be treated like a simple insert perhaps? If that fails (I'm assuming insert before trigger has the ability to stop the insert) the entire statement (including the implicit delete) will be rolled back since a single statement implies a single transaction? Kind regards, Jaco