This is not another "how to prevent insert with triggers" thread. I have done my research and I found a couple of ways but unfortunately, they won't work in my case.
I have a table in my database with a numeric column called "value". I want to cancel a new insert if its value is near the value of a previous insert. So here are my attempts:
IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN SET new.val = null; END IF
Results: It works well for one insert (throws an error and cancels the insert). However, for multiple inserts at once, all records are inserted (and the inserts that should be rejected have value 0).
IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN SIGNAL SQLSTATE '45000'; END IF
Results: Again, works well for one insert. But for multiple inserts at once, it will reject all inserts even if there is only one that should be rejected.
I hope I was clear. Thank you in advance!