Hi Alex, 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:
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}$') Correct. It does its own conflict detection as UNIQUE keys can't represent it. I wasn't thinking that the CONSTRAINT needs to be UNIQUE, the above is a CHECK ... which could potentially use a stored function to perform the check ... IIRC with MariaDB, it is not possible to query the table within a CHECK CONSTRAINT. That would actually make sense.
What the trigger should do in pseudocode:
IF EXISTS (SELECT * FROM {horrifying conditions} ) THEN IF {IS_INSERT} OR {further validation for REPLACE} THEN SIGNAL SQLSTATE '45000' MESSAGE_TEXT = 'Row is invalid...'; END IF; END IF;
This is ~ what I understood. I can't find a way to determine the {IS_INSERT} above, however (from https://mariadb.com/kb/en/trigger-overview/): "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. Depending on frequency of OK vs NOT OK updates the performance could be horrendous overall though ... but if we assume that the bulk of queries won't be problematic this should probably be OK. Probably not helpful, so sorry if I'm just causing noise, I find this an extremely interesting problem. Kind regards, Jaco