MySQL trigger: print a warning message when an inserted entry is greater than a value
15,175
Couple of things wrong here.
- 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.
- Location of the BEGIN statement. It should be directly after FOR EACH ROW.
- Use of WHEN instead of IF.
- 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 ;
Author by
Vahid Mirjalili
Machine Learning, and Deep-learning Computer Vision Co-author of Python Machine Learning, 2nd Ed.
Updated on June 27, 2022Comments
-
Vahid Mirjalili almost 2 years
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