mySQL trigger to prevent insert won't work

Question

The problem has been discussed before but the answers won't work in my case.

I have a table in my database which contains a numeric column called "value".

I would like to prevent an insert if the new.value is close to the value of a previous insert. So here is the main part of the BEFORE trigger:

first try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SET new.value = null;
END IF

Result: for one insert, it will throw an error and it will work as expected. However, for multiple inserts at once, the inserts that should be prevented are there, with value 0.

second try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SIGNAL SQLSTATE '45000';
END IF

Result: again, for one insert, it works as expected. However, for multiple inserts at once, it will also prevent the inserts that should be inserted.

I hope I was clear enough. Thank you in advance!

Answer 1

Change EXISTS( SELECT ... ) to EXISTS( SELECT ... FOR UPDATE ).

(Caveat: I am guessing that this may solve it.)