CODEWITHSUNDEEP
mysqldatabase-trigger
iProgram
iProgram

Reputation: 6577

MySQL trigger not checking variable correctly

I am new to MySQL triggers and would like to prevent entries from being stored in the database if they are invalid. In this case, I would like to check if the module year is greater than the number of years in a course.

Here is my procedure (This is executed on before insert).

SELECT num_of_years INTO @num_years FROM courses WHERE courses.course_id = NEW.course_id;

IF NEW.course_year > @num_years THEN
UPDATE `Error: invalid_id_test` SET x=1;
END;

Why is this allowing entry of any course year and how do I solve it?

Upvotes: 3

Views: 43

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562811

Edit: Revised answer now that I understand the problem better.

Here's a test.

create table courses (
  course_id int primary key,
  num_of_years tinyint unsigned default 1
);

create table modules (
  module_id int primary key,
  course_id int,
  course_year tinyint unsigned
);

delimiter ;;
create trigger t before insert on modules for each row 
begin 
  declare num_years tinyint unsigned; 
  select num_of_years into num_years from courses where course_id = NEW.course_id; 
  if NEW.course_year > num_years then 
    signal sqlstate '45000' 
      set message_text = 'course_year is larger than the course length'; 
  end if; 
end;;
delimiter ;

This kind of works:

insert into courses set course_id=1, num_of_years=3;

insert into modules values set module_id=1, course_id1, course_year=4;
ERROR 1644 (45000): course_year is larger than the course length

But it doesn't prevent the INSERT if the courses.num_of_years is NULL.

insert into courses set course_id=2, num_of_years=NULL;

insert into modules set module_id=2, course_id=2, course_year=99;
Query OK, 1 row affected (0.01 sec)

The reason is that the variable in the trigger is NULL, therefore NEW.course_year > num_years is not true and the exception is not thrown.

To fix this, check for NULL.

delimiter ;;
create trigger t before insert on modules for each row 
begin 
  declare num_years tinyint unsigned; 
  select num_of_years into num_years from courses where course_id = NEW.course_id; 
  if num_years is NULL or NEW.course_year > num_years then 
    signal sqlstate '45000' 
      set message_text = 'course_year is larger than the course length'; 
  end if; 
end;;
delimiter ;

insert into modules set module_id=2, course_id=2, course_year=99;
ERROR 1644 (45000): course_year is larger than the course length

This also throws an error if you try to insert a module for a course_id that is not found. Again, this will make the num_years NULL, so we need the check for that in our trigger.

insert into modules set module_id=2, course_id=5, course_year=99;
ERROR 1644 (45000): course_year is larger than the course length

Upvotes: 2

Related Questions