CODEWITHSUNDEEP
mysqlsqlmysql-workbench
SNM
SNM

Reputation: 6795

MYSQL Workbench - Trigger does not check for my insertion

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.

Upvotes: 1

Views: 451

Answers (2)

Nikhil
Nikhil

Reputation: 3950

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!

Upvotes: 0

Alfabravo
Alfabravo

Reputation: 7569

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 ;

Upvotes: 1

Related Questions