So I'm trying to do the following: one table, queries like:
INSERT INTO ... (multiple), (values) ON DUPLICATE KEY UPDATE ...
so far this works just fine, no problem. Now I would like to add a trigger which does the following: if NEW.field = 0 it deletes a row from the table (with same primary key) AND prevents the INSERT/UPDATE operation. And this is where I'm lost. How to prevent inserting/updating only that SINGLE row? My understanding is that if I do
SIGNAL SQLSTATE ... that would break the whole statement, so none of the records would be inserted/updated, which is not what I want.
I was thinking doing:
INSERT INTO IGNORE... (multiple), (values) ON DUPLICATE KEY UPDATE ... (notice: IGNORE)
but... would this work fine? Does
IGNORE ignores the errors for both insert AND the update trigger as well?
I know the obvious (and easy) workaround would be to just use
AFTER triggers instead of
BEFORE, but since it's a busy table with many thousands of insertion/update per second I wouldn't really want to do that.