Use a trigger to stop an insert or update

mysql, triggers

Solution

Try the SIGNAL syntax - https://dev.mysql.com/doc/refman/5.5/en/signal.html

create trigger agency_check
before insert on foo
for each row
begin
  if (new.agency < 1 or new.agency >5) then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'your error message';
  end if 
end

EDIT

Updated based on popular comment below by Bill Karwin.

Problem

Since MySQL ignores check constraints, how does one go about using a trigger to stop an insert or update from happening? For example: Table foo has an attribute called agency, and the agency attribute can only be 1, 2, 3, 4, or 5. ``` delimiter $$ create trigger agency_check before insert on foo for each row begin if (new.agency < 1 or new.agency > 5) then #Do nothing? end if; end $$ delimiter ; ``` Or is there a better way to go about doing check constraints in MySQL?

Original source