mySQL trigger - prevent insert won't work for multiple inserts

Question

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:

first try:

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).

second try:

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.

Any suggestions?

I hope I was clear. Thank you in advance!

• mustaccio: By "multiple inserts at once", do you mean a single INSERT statement that inserts more than one row, or multiple concurrent sessions each inserting one row?
• RDFozz: If val has a unique index on it - what if you changed new.val to an existing val where they're close? Would that still prevent all inserts, or would the non-existing val values go through? NOTE: you would still have an issue if multiple records in the data to insert have values that are too close. (Not making this an answer at this point - don't know MySQL well enough to know if it's a possibility. If it is, I'll convert).
• treecon: @mustaccio I mean a single INSERT that inserts more than one row.
• mustaccio: Then this is expected behaviour: if you issue SIGNAL, the entire statement is rolled back; if you set new.val to NULL, you insert NULL (I guess your client is messing with you by showing zeroes for NULL values).
• treecon: @RDFozz unfortunately, it does not work. It prevents all inserts.

Answer 1

This is expected behaviour: if you raise a SIGNAL, the entire statement is rolled back; if you set new.val to NULL, you insert NULL (I guess your client is messing with you by showing zeroes for NULL values).

You could work around this problem by not inserting ineligible records in the first place, something like this:

insert into test2 (val) select val from (
 -- values to insert
 select 1 as val union all 
 select 3
) i 
where not exists (
 select 1 from test2 t where abs(t.val - i.val) <= 1
)