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!

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

1 Answer 1

0

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

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

1
  • 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, 2017 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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