mysql 创建触发器语法错误 [英] mysql create trigger syntax error

问题描述

我检查了很多 SO 线程(其中一个此处)但找不到问题所在.

I've check many SO threads (one of them here) but couldn't find where the issue lies.

如果列不为空，我试图保护它不被更新，遵循 这个线程.

I am trying to protect a column from being updated if it's not null, following this thread.

但我收到来自 mysql 的语法错误.这是我的代码:

But I am getting syntax error from mysql. Here's my code:

DELIMITER $$

CREATE TRIGGER lock_x_id
BEFORE UPDATE ON Games
FOR EACH ROW BEGIN
  IF (old.xid IS NOT NULL) THEN
    SIGNAL 'error';
  END IF;
END$$

DELIMITER ;

推荐答案

当您尝试通过 SIGNAL 引发错误时，您需要指定 SQLSTATE 这是错误代码对于用户定义的通用错误代码，它的 45000 以及消息文本 MESSAGE_TEXT

When you try to raise errors via SIGNAL you need to specify the SQLSTATE which is the error code and for the user defined generic error codes its 45000 along with the message text MESSAGE_TEXT

所以触发器变成了

delimiter //
create trigger lock_x_id before update on games
for each row
begin
 if old.xid is not null then
   signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';
 end if;
end;//
delimiter ;

测试用例

mysql> select * from games;
+----+------+------+
| id | xid  | val  |
+----+------+------+
|  1 | NULL |    1 |
|  2 | NULL |    2 |
|  3 | NULL |    3 |
|  4 |    1 |    4 |
|  5 |    2 |    5 |
+----+------+------+

让我们现在创建触发器

mysql> delimiter //
mysql> create trigger lock_x_id before update on games
    -> for each row
    -> begin
    ->  if old.xid is not null then
    ->    signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';
    ->  end if;
    -> end;//
Query OK, 0 rows affected (0.05 sec)


mysql> update games set xid = 4 where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update games set xid = 5 where id=5;
ERROR 1644 (45000): Your custom error message

在运行上述 2 个更新命令后，表格的外观

And after running the above 2 update commands here how the table looks

mysql> select * from games;
+----+------+------+
| id | xid  | val  |
+----+------+------+
|  1 |    4 |    1 |
|  2 | NULL |    2 |
|  3 | NULL |    3 |
|  4 |    1 |    4 |
|  5 |    2 |    5 |
+----+------+------+

注意第二次更新失败，行没有变化.

Note that the 2nd update failed and the row is unchanged.

阅读有关此内容的更多信息 https://dev.mysql.com/doc/refman/5.5/en/signal.html

Read more about this https://dev.mysql.com/doc/refman/5.5/en/signal.html

这篇关于mysql 创建触发器语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！