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屋!