0

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!

  • 2
    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? – mustaccio May 30 '17 at 14:16
  • 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). – RDFozz May 30 '17 at 14:31
  • @mustaccio I mean a single INSERT that inserts more than one row. – treecon May 30 '17 at 14:44
  • 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). – mustaccio May 30 '17 at 15:28
  • @RDFozz unfortunately, it does not work. It prevents all inserts. – treecon May 30 '17 at 16:31
0

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.