MySQL trigger: print a warning message when an inserted entry is greater than a value

Question

I have create a table as below:

mysql> create table testa (a int, b int, c real);
Query OK, 0 rows affected (0.14 sec)

But when I want to implement a trigger like this, I face some syntax errors:

mysql> create trigger testa_trig  
       before insert ON testa 
       FOR EACH ROW 
       WHEN (NEW.c > 100) 
       BEGIN 
         Print "Warning: c > 100!"
       END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN (NEW.c > 100) 
BEGIN
Print "Warning: c > 100!"
END' at line 4

I have checked the documentation at http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html but can't figure out the problem!

My MySQL version:

Server version: 5.5.38-0ubuntu0.12.04.1 (Ubuntu)

Based on the comments below, I tried the following cases, but also crashed:

mysql> create trigger testa_trig before insert on testa for each row 
       if (NEW.c > 100) begin insert into testb set bc=NEW.c end;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'begin insert into testb set bc=NEW.c end' at line 1

Answer 1

Couple of things wrong here.

1. Delimiters. When you make a MySQL procedure or trigger, you need to be very explicit about delimiters so the query interpreter can distinguish between ends of lines in your procedure and the end of your declaration.
2. Location of the BEGIN statement. It should be directly after FOR EACH ROW.
3. Use of WHEN instead of IF.
4. Use of PRINT instead to SIGNAL SQLSTATE '...' SET MESSAGE_TEXT = '...'. This is how you raise exceptions in MySQL 5.5+.

Here is code that should work!

DELIMITER $$

CREATE TRIGGER testa_trig  
BEFORE INSERT ON testa 
FOR EACH ROW BEGIN
    IF (NEW.c > 100) THEN 
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning: c > 100!';
    END IF;
END$$

DELIMITER ;