使用触发器来停止 MySQL 中的插入或更新?

使用触发器来停止 MySQL 中的插入或更新?

您需要使用 SIGNAL SQL STATE 命令来停止 MySQL 中的插入或更新。触发器语法如下:

DELIMITER // CREATE TRIGGER yourTriggerName BEFORE INSERT ON yourTableName FOR EACH ROW BEGIN yourCondition THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'anyMessageToEndUser'; END // DELIMITER ;登录后复制

mysql> DELIMITER // mysql> CREATE TRIGGER InsertPreventTrigger BEFORE INSERT ON Insert_Prevent -> FOR EACH ROW -> BEGIN -> IF(new.Id 5) THEN -> SIGNAL SQLSTATE '45000' -> SET MESSAGE_TEXT = 'You can not insert record'; -> END IF; -> END // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;登录后复制

现在让我们先创建一个表。创建表的查询如下:

mysql> create table Insert_Prevent -> ( -> Id int -> ); Query OK, 0 rows affected (0.62 sec)登录后复制

mysql> insert into Insert_Prevent values(0); ERROR 1644 (45000): You cannot insert record mysql> insert into Insert_Prevent values(6); ERROR 1644 (45000): You cannot insert record登录后复制

mysql> insert into Insert_Prevent values(1); Query OK, 1 row affected (0.20 sec) mysql> insert into Insert_Prevent values(5); Query OK, 1 row affected (0.17 sec) mysql> insert into Insert_Prevent values(2); Query OK, 1 row affected (0.11 sec) mysql> insert into Insert_Prevent values(3); Query OK, 1 row affected (0.23 sec)登录后复制

mysql> select *from Insert_Prevent;登录后复制

+------+ | Id | +------+ | 1 | | 5 | | 2 | | 3 | +------+ 4 rows in set (0.00 sec)登录后复制