MySQL Trigger to prevent INSERT under certain conditions

Question

I want to make a trigger that will prevent the insertion if the birthdate (one of the columns) is in the future. I have this:

CREATE TRIGGER foo BEFORE INSERT ON table FOR EACH ROW BEGIN   IF NEW.birthdate > CURRENT_DATE()   THEN     //How do I prevent the insert right here???   END IF; END; 

How can I cancel the insert inside the if statement?

Answer 1

Example 1, MySQL, You can cancel that insert in the trigger with signal sqlstate

1. Create your table with a varchar column:

   mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec) 

2. Create your 'before insert' trigger to check for a condition and disallow.

   mysql> delimiter $$ mysql> create trigger foo before insert on yar     -> for each row     -> begin     -> if new.val = '' then     -> signal sqlstate '45000';     -> end if;     -> end;$$ Query OK, 0 rows affected (0.01 sec) 

3. Try to insert where the condition is met:

   mysql> delimiter ;  mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition  mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec)  mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec) 

You inserted a blank string, the trigger saw it was blank and raised the signal to prevent the insert.

Example 2, MySQL, Cancel the insert in the trigger by causing the data to violate a not null constraint.

1. Create your table with a varchar column:

   mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec) 

2. Create your 'before insert' trigger to check for a condition and disallow.

   mysql> delimiter $$ mysql> create trigger foo before insert on yar     -> for each row     -> begin     -> if new.val = '' then     -> set new.val = NULL;     -> end if;     -> end;$$ Query OK, 0 rows affected (0.01 sec) 

3. Try to insert where the condition is met:

   mysql> delimiter ;  mysql> insert into yar values(""); ERROR 1048 (23000): Column 'val' cannot be null  mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec)  mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec) 

You inserted a blank string, the trigger saw it was blank and changed the value to null, so the insert is prevented.

Answer 2

I don't know if it's to late but you can do that now :

SIGNAL SQLSTATE '45000'  SET MESSAGE_TEXT = "your error text"; 

P.S. Don't forget to modify the delimiter before and after the trigger...