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!

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
)