mysql触发器如果提示,MySQL触发器：当插入的条目大于某个值时,显示警告消息

我创建了一个表,如下所示：

mysql> create table testa (a int, b int, c real);

Query OK, 0 rows affected (0.14 sec)

但是,当我想实现这样的触发器时,我会遇到一些语法错误：

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

我的MySQL版本：

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

根据以下评论,我尝试了以下情况,但也崩溃了：

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

解决方法:

这里有几处错误.

>分隔符.在创建MySQL过程或触发器时,需要非常明确地说明定界符,以便查询解释器可以区分过程中的行尾与声明的末尾.

> BEGIN语句的位置.它应该直接在FOR EACH ROW之后.

>使用WHEN代替IF.

>使用PRINT代替为SIGNAL SQLSTATE’…’SET MESSAGE_TEXT =’…’.这就是在MySQL 5.5中引发异常的方式.

这是应该起作用的代码！

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 ;

标签：mysql,database

来源： https://codeday.me/bug/20191121/2050006.html