2

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!

  • new.value is a user variable, value is a variable from the column of the table. Is my understanding is right? – Mannoj May 30 '17 at 4:58
0

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

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

| improve this answer | |
  • in trigger? think it will not work. situation look like error in MySQL - it always check all NEW.values, not current – a_vlad May 30 '17 at 5:53

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.