MySQL MySQL触发器在某些条件下防止插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2981930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger to prevent INSERT under certain conditions
提问by Igor
I want to make a trigger that will prevent the insertion if the birthdate (one of the columns) is in the future. I have this:
如果生日（其中一列）在未来，我想制作一个触发器来阻止插入。我有这个：
CREATE TRIGGER foo
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.birthdate > CURRENT_DATE()
THEN
//How do I prevent the insert right here???
END IF;
END;
How can I cancel the insert inside the if statement?
如何取消 if 语句中的插入？
采纳答案by Rich Adams
Based on thisI'm not sure if it's possible to do it that way.
基于此，我不确定是否可以这样做。
There's no support in MySQL's current implementation of triggers to voluntarily throw an exception and abort the statement that spawned the trigger.
The workaround I've found is to write a BEFORE trigger to set one of the not-NULL columns in the table to NULL, thus violating its NOT NULL constraint. This causes the statement that spawned the trigger to be aborted.
MySQL 当前的触发器实现不支持自愿抛出异常并中止产生触发器的语句。
我发现的解决方法是编写一个 BEFORE 触发器以将表中的非空列之一设置为 NULL，从而违反其 NOT NULL 约束。这会导致产生触发器的语句被中止。
回答by Eric Leschinski
Example 1, MySQL, You can cancel that insert in the trigger with signal sqlstate
示例 1，MySQL，您可以取消触发器中的插入操作 signal sqlstate
Create your table with a varchar column:
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)Create your 'before insert' trigger to check for a condition and disallow.
mysql> delimiter $$ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> signal sqlstate '45000'; -> end if; -> end;$$ Query OK, 0 rows affected (0.01 sec)Try to insert where the condition is met:
mysql> delimiter ; mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
使用 varchar 列创建表：
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)创建“插入前”触发器以检查条件并禁止。
mysql> delimiter $$ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> signal sqlstate '45000'; -> end if; -> end;$$ Query OK, 0 rows affected (0.01 sec)尝试在满足条件的地方插入：
mysql> delimiter ; mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
You inserted a blank string, the trigger saw it was blank and raised the signal to prevent the insert.
您插入了一个空白字符串，触发器看到它是空白的并发出信号以防止插入。
Example 2, MySQL, Cancel the insert in the trigger by causing the data to violate a not null constraint.
示例 2，MySQL，通过导致数据违反非空约束来取消触发器中的插入。
Create your table with a varchar column:
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)Create your 'before insert' trigger to check for a condition and disallow.
mysql> delimiter $$ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> set new.val = NULL; -> end if; -> end;$$ Query OK, 0 rows affected (0.01 sec)Try to insert where the condition is met:
mysql> delimiter ; mysql> insert into yar values(""); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
使用 varchar 列创建表：
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)创建“插入前”触发器以检查条件并禁止。
mysql> delimiter $$ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> set new.val = NULL; -> end if; -> end;$$ Query OK, 0 rows affected (0.01 sec)尝试在满足条件的地方插入：
mysql> delimiter ; mysql> insert into yar values(""); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
You inserted a blank string, the trigger saw it was blank and changed the value to null, so the insert is prevented.
您插入了一个空白字符串，触发器看到它是空白并将值更改为 null，因此插入被阻止。
回答by Black Butterfly
I don't know if it's to late but you can do that now :
我不知道是否为时已晚，但您现在可以这样做：
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "your error text";
P.S. Don't forget to modify the delimiter before and after the trigger...
PS不要忘记修改触发器前后的分隔符...
回答by mugume Johnson
DELIMITER $$
CREATE TRIGGER foo
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.birthdate > CURRENT_DATE()
THEN
SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Warning: birthdate can not be greater than current date!';
END IF;
END$$
DELIMITER ;
回答by Mahdi J.Ansari
I had the same problem. I created a table but there was no way to assign the minimum length of string in MySQL. I created the table as follows:
我有同样的问题。我创建了一个表，但无法在 MySQL 中分配字符串的最小长度。我创建的表如下：
CREATE TABLE Users
(
UserName varchar(15) NOT NULL PRIMARY KEY,
Password varchar(15) NOT NULL,
Active Bool DEFAULT TRUE,
CHECK (CHAR_LENGTH(UserName)>3)
)
But the CHECK function doesn't work on insert.
但是 CHECK 函数在插入时不起作用。
So I changed my Table as follow :
所以我改变了我的表如下：
CREATE TABLE Users
(
UserName varchar(15) NOT NULL PRIMARY KEY,
Password varchar(15) NOT NULL,
Active Bool DEFAULT TRUE
)
After that, create a Trigger:
之后，创建一个触发器：
delimiter $$
CREATE TRIGGER myTrigger
BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
IF CHAR_LENGTH(NEW.UserName) < 4 THEN
DELETE FROM Users WHERE Users.UserName=NEW.UserName;
END if;
END$$
delimiter ;
回答by MaHuJa
The problem described in the question sounds like a perfect candidate for a CHECK constraint - add this line to the table definition.
问题中描述的问题听起来像是 CHECK 约束的完美候选 - 将此行添加到表定义中。
CONSTRAINT born_in_the_past CHECK (birthdate<=now())
约束born_in_the_past CHECK (birthdate<=now())
