MySQL trigger not checking variable correctly

Question

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?

@DanB, that's from 2010, and it's long out of date. MySQL 5.5 and later supports SIGNAL as a way of throwing exceptions from within a trigger. — Bill Karwin
– Bill Karwin, Commented Jan 14, 2019 at 19:27

Bill Karwin · Accepted Answer · 2019-01-14 19:49:55Z

2

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

edited Jan 14, 2019 at 19:49

answered Jan 14, 2019 at 19:26

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iProgram Over a year ago

This is in the modules table. In the modules table, I would like to make sure if the module year is less than or equal to the course length.

Bill Karwin Over a year ago

Oh, so course_year is like 1, 2, 3... not like 2018, 2019, 2020?

Collectives™ on Stack Overflow

MySQL trigger not checking variable correctly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related