Can a MySQL trigger simulate a CHECK constraint? [duplicate]

Question

I want to use the CHECK constraint in MySQL, but it is not supported. (Unlike other RDBMS, it will understand but not enforce the CHECKs.)

I have seen some workarounds with triggers. But they tend to set a default value to the field in question instead of returning an error.

Is it possible to construct a trigger that returns an error if a condition is not met?

Ultimately I want a trigger that copies a CHECK constraint.

Answer 1

when you are updating data :

delimiter $$
create trigger chk_stats1 before update on stats 
  for each row 
   begin  
    if  new.month>12 then
        SIGNAL SQLSTATE '45000'   
        SET MESSAGE_TEXT = 'Cannot add or update row: only';
      end if; 
      end; 
      $$

when you are inserting data :

   delimiter $$
    create trigger chk_stats before insert on stats 
      for each row 
       begin  
      if  new.month>12 then
       SIGNAL SQLSTATE '45000'   
       SET MESSAGE_TEXT = 'Cannot add or update row: only';
       end if; 
    end; 
    $$

these trigger will work as check constraint ,work before insert or update and check month, if month >12 gives error .