1

I have created a basic trigger that checks if the the new value inserted into a row is greater or equal than 1 and less or equal than 10 , this is for just test qualifications , the thing is that wherever I just insert a new test qualification for example 0 it adds it up to the table, but my trigger checks that this value should be greater than 0 and is not working

Trigger that checks if a qualification is greater or equal than 1 or less or equal than 10

delimiter //
CREATE TRIGGER checkQlfy BEFORE
INSERT ON qualify
FOR EACH ROW
BEGIN
IF new.qualify_pp >= 1 and new.qualify_pp <= 10 THEN  
INSERT INTO qualify(qualify_pp) VALUES (new.qualify_pp);
END IF;
END//

delimiter ;

Now, this trigger is created succefully but wherever I insert a new value lets say with this line

INSERT INTO qualify(qualify_pp) VALUES(0);

It is inserted into the table, but I have said in the trigger that values greater or equal than 1 should be added.

I dont know why is this happening.

SNM
  • 5,625
  • 9
  • 28
  • 77

2 Answers2

1

You have two options:

  • Add a CHECK constraint over the qualify_pp field. Only works in versions >= 8.0.16 (here's why).

  • You cannot change the insert operation triggering the trigger. You can, however, raise an error if a validation fails so the insert is not performed.

E.g. (see how the validations in the IF changed):

delimiter //
CREATE TRIGGER checkQlfy BEFORE
INSERT ON qualify
FOR EACH ROW
BEGIN
IF new.qualify_pp < 1 OR new.qualify_pp > 10 THEN  
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not allowed / some message';
END IF;
END//

delimiter ;
Alfabravo
  • 7,493
  • 6
  • 46
  • 82
  • Excelent ! so, I should alter the table qualify to place a check(qualify_pp >= 1 and qualify_pp <= 10), is that right ? – SNM Apr 11 '19 at 19:05
  • Can you please guide me how to add that check constrain ? because I tried altering the table buy is not working – SNM Apr 11 '19 at 19:11
  • @CoffeeBreak Yes, that was the approach initially suggested by Nikhil. It is simpler and, if you don't need extra complexity in the validations, a CHECK should do. Triggers are always more tricky, especially in MySQL. Also, please tell us which MySQL version are you using, as CHECK constraints are supported in MySQL >= 8.0.16 – Alfabravo Apr 11 '19 at 19:12
  • this does not work ALTER TABLE notas ADD CONSTRAINT nota_pp CHECK (nota_pp >= 1 and nota_pp <= 10); and I have mysql to the 8 version – SNM Apr 11 '19 at 19:36
  • @CoffeeBreak version 8, which one? Minor version is important. Also, "does not work" is not enough; does it fail to execute the ALTER TABLE? It executes but does nothing when inserting? Please specify – Alfabravo Apr 11 '19 at 19:47
  • it executes and does not fail, but it seems the check dosnt work – SNM Apr 11 '19 at 20:42
  • @CoffeeBreak check the exact MySQL version, because before CHECK constraints were supported, they were silently ignored. – Alfabravo Apr 11 '19 at 20:44
0

The trigger you have written would do the insert based on:

1) insert statement and trigger insert if 1<=value<=10
2) insert statement and trigger not insert if 1>=value>=10

You would have to add a check constraint in it, rather than having a trigger!

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Nikhil S
  • 3,786
  • 4
  • 18
  • 32